Normalisation Question

[Meltdown]

Hi, I'm building an Invoices table and I'm not sure if I need to normalise the table further; this is the scenario

There are two types of Invoices being entered, Purchases and Sales.

If the option Sales is selected then there would be some fields that are only relevant to Sales invoices, e.g Date_Sent, Project_ID etc

If the option Purchases is selected then there would be some fields that are only relevant to Purchases invoices, e.g Received_Date, Due_Date, Supplier_ID etc

Question: Should the Invoices table be split into 2 table one for Sales the other for Purchases?

Fields in the current Table:
Invoice_ID
Office_ID
Supplier_ID
Project_ID
InvoiceType
Invoice_Num
Sent_Date
Received_Date
Due_Date
Terms
Currency
Exchange_Rate
Tax_Deductable
Amount
VAT_Rate
Discount
Invoice_Category
Comment
Paid
Paid_Date

Thanks for any advice

 

[theprez]

I would probably add an InvoiceType field that has a P or S. You can do your users a favor by automatically filling in the field with what type of invoice they are entering. Do this by creating two forms, one for purchases and one for sales. Depending on what form they are in, you can have the default for the InvoiceType field be a P or S. By separating the forms, you can also display only the fields that are relevant to the type of invoice being entered.

 

[Pat Hartman]

Question: Should the Invoices table be split into 2 table one for Sales the other for Purchases?

- It depends

Do you need to have the data in a single recordset for reporting?

Are you ultimately going to spend more time putting the two tables together and are you going to end up creating numerous duplicate (almost) items if you keep the data in two tables? Putting them together will affect forms/reports that need to vary what they show. This code to hide/show things is simple and may end up easier than the alternative.

 

[Meltdown]

Thanks for the replies, I think I'll keep it as one table.