Question Simplifying a large transaction table

[kev88]

Hi there,

It's been many years since I first created a database from scratch so I was looking for a quick refresher.

I have one large transaction table with the following fields:

Transaction Date
Sales invoice number
Transaction Details
Product Type
Customer Reference
Customer Name
Customer Address
Customer Phone
Product Code
Unit Cost
Selling Price
Quantity
Sales Area
Sales Rep Forename
Sales Rep Surname
Sales Rep Phone Number

So to simplify this table through normalisation, would I do the following:

Main table

Customer Reference *
Sales Invoice Number *
Sales Rep ID *

Tables branched off this

tblCustomer
1) Customer Reference *
Customer Name
Customer Address
Customer Phone

tblSalesInvoice
2) Sales Invoice Number *
Transaction Date
Transaction Details
Product Code (linked to table 4)
Quantity
Sales Invoice Total
Sales Invoice Profit

tblSalesRep
3) Sales Rep ID
Sales area
Sales Rep Forename
Sales Rep Surname
Sales Rep phone number

tblProduct
4) Product ID
Product Type
Unit Cost
Selling price
Profit per unit


Am I on the right lines? It doesn't look right to me so any help would be excellent.
Thanks for your time

 

[DavidAtWork]

To achieve the best level of normalisation, you have to think terms of the relationships between all your main objects ie customers, products, salesReps, transactions & sales invoices and determine which are 1:1 and which are 1:many. Where ever you know it will be a 1:many, then a separate table will be needed to store these records.
Looking at your data above, you have to ask yourself:
can a transaction have many invoices
can an invoice have many products

I would assume a transaction can only have one customer, one sales rep so the main table will be something like:
transactionID - PK
transDate
transRef
customerID
salesRepID
invoiceID (if only ever one invoice per trans, otherwise a linkID to a table that stores multiple invoices to one transaction)

Your salesInvoice table doesn't look right to me, should be
invoiceID - PK
total price
productID (if only ever one product per invoice, otherwise a linkID to a table that stores multiple products to one invoice)
plus any other details that relate only to that invoice not stored in any other table

Hope that's given you some ideas
David

 

[kev88]

In essence, the structure would be something like

Main table

transactionID - PK
transDate
transRef
customerID
salesRepID
invoiceID

tblCustomer

Customer Reference - PK
Customer Name
Customer Address
Customer Phone

tblSalesRep

Sales Rep ID - PK
Sales area
Sales Rep Forename
Sales Rep Surname
Sales Rep phone number

tblProduct

Product ID - PK
Product Type
Unit Cost
Selling price
Profit per unit

tblSalesInvoice

invoiceID - PK
total price
productID



I checked the database table and each invoice has many products. So how would I create a table for this and what fields would it contain? Sorry about all the questions but I appreciate your help with this

 

[Kiwiman]

Howzit

You will need a Invoice Header table and an Invoice Detail table. The minimum you will need is. I suggest looking at the Northwind - particulary the Purchase Order Form which shows you how this sort of thing fits together.

Invoice Header

• InvoiceID <PK>
• CustomerID <FK>
• TrxDate
• SalesPersonID <FK>
• InvoiceType (assumes you may have Returns and Sales)

Invoice Line

• InvoiceLineID <PK>
• InvoiceID <FK>
• ProductID <FK>
• Quantity
• UnitPrice
• SalesTax

The extended sale value is a calculated field based on unitprice and quantity and would NOT be stored in a table itself.

 

[kev88]

Ok thanks for your help

So the invoice header above will replace my tblSalesInvoice?

And the "invoice line" table above is used for multiple products in the database?

Hope I've got it right!

 

[Kiwiman]

Howzit

Yes that's right. An invoice can have one or many sale lines to it which is why you need this setup.

 

[kev88]

Ok great.

Whilst looking at my existing table, it doesn't have a line number for invoices. How would I go about creating this? There are over 1,500 transactions in the table at the moment and these need to be integrated into the new system.

 

[Kiwiman]

Howzit

If you don't want this to mean anything, you can use the autonumber Data Type for this field (this is what I do) as the way of identifying a unique line record. This will self populate when you upload the data to the line table.

It wil be the Invoice ID that will link it to the Invoice Header

 

[Simon_MT]

What is TransDetails?

Simon

 

[kev88]

TransDetails details is what was sold (e.g. Desk Lamp). Does this make a difference?

Also, I'm trying to identify the entities and relationships. I maybe should've done this before.
I take it the entities would just be Invoice Line, Invoice Header, Sales Rep, Product, Customer and Transaction (i.e. the names of the tables above), then you do relationships between them such as one to many? For example, a customer can do many transactions, and a transaction is carried out by one customer, so that would be a one-to-many relationship.
Sorry for all the questions - it's just I want to get the underlying structure correct.

 

[kev88]

Ok, here's the final structure I've came up with. I don't think there is a need for that "main table" I have above since all the info is already contained within the following tables I think?

tblCustomer

Customer Reference <PK>
Customer Name
Customer Address
Customer Phone

tblSalesRep

Sales Rep ID <PK>
Sales Area
Sales Rep Forename
Sales Rep Surname
Sales Rep phone number

tblProduct

Product ID <PK>
Product Type
Unit Cost
Selling price
Profit per unit

tblInvoice

InvoiceID <PK>
CustomerID
TrxDate
SalesRepID

tblItemsOnInvoice

InvoiceID <PK>
ProductID <FK>
Quantity
SellingPrice

Thanks for your help