View Single Post
  #6 (permalink)  
Old June 14th, 2004, 12:16 AM
Justin Cave Justin Cave is offline
Authorized User
Join Date: Apr 2004
Location: Boise, ID, USA.
Posts: 70
Thanks: 0
Thanked 0 Times in 0 Posts

If you want each invoice to start fresh with line 1, line cannot be the primary key in your item table. Perhaps line and invoice together would make a good primary key (a natural primary key), perhaps you should add a column to the item table like "item_id" with is the primary key and which is populated by a sequence (synthetic primary key). Whether the natural or the synthetic primary key is the better approach depends on a number of factors.

- Do you generally use natural or synthetic primary keys?
- Would you ever envision changing the invoice number or line of a particular item? Or would those columns, once set, remain static for the life of the item?
- Is there benefit to having a single column be the primary key from a data model standpoint? If lots of tables or particularly large tables need to have a foreign key constraint to the item table, it may be easier to have a single column rather than having a composite primary key for data storage reasons as well as for clarity of the model.

Distributed Database Consulting, Inc.