JOin TABles - UNderstanding or the lack thereof

[imoree]

Hello everyone;

i am a bit annoyed that i just dont understand the join table from a tutorial that i have been reading. It is an invoicing solution which is great, but the instructor doesnt really go into depth explaining why certain things are in the invoices table & not in the line items table.

Can someone please explain to me why you would have:

• price - *main data in invoices table ( lookup in lineitems table)
  
• qty - only in lineitems table , not in invoices table
  
• extended price ( only in line items table)
  
• total of invoice - (only on line items table)
  

i am sure someone can explain reasonably simply what the join table(s) purpose is and what data NEEDS to be in that join

table.

Thank you for sharing with a curious newb as myself.

--i

[comment]

price - *main data in invoices table ( lookup in lineitems table)

Price does not go into the Invoices table. You need a Price field in the Products table - this is the current price of a product. When a product is being sold, you lookup (i.e. copy) the price from the product record into the Price field in Lineitems. This ensures that subsequent price changes do not affect past sales. It also enables you to modify the price manually for a specific sale.

Quantity goes into the LineItems table, because each line item can have a different quantity. The Invoices table should have only fields that describe the entire invoice - such as date, tax rate (assuming one rate for all items), etc. For the same reason, the extended price must be calculated in the LineItems table (each line has its own quantity and its own price).

The total of invoice can be a calculation field in the Invoices table, or a summary field in the LineItems table - or both.

See also:

http://fmforums.com/forum/showpost.php?post/309136/

[Scott Howard]

I started a series of articles on foundational database topics on my web site. These grew from my hosting of a local developers group and coaching other developers over many years. I hope to expand on these some day... but you know what they say about "good intentions!"

The Join Table - or the "Many-to-Many" relationship that the join makes possible - is a difficult concept to grasp. I posted an article with simple illustrations that might help you:

http://scotthowardconsulting.com/?p=88

[imoree]

I think i finally UNDERSTAND joins and their purpose & thank you both..