AutoNumber vs Compound Primary Keys.

Dear Access Expert.

I was looking at a project today and the developer had a table with a primary key composed of 3 fields.

His table schema had the following primary keys.

tblOrder:
OrderID

tblOrderLines:
OrderID, LineNum

tblOrderLineDetails:
OrderID , LineNum, LineItemNum


I would have done the following.

tblOrder:
OrderID

tblOrderLines:
AutoNumberLineID (Just regular Fields: OrderID, LineNum)

tblOrderLineDetails
AutoNumberLineItemID (AutoNumberLineID, LineItemNum )

My schema allows for Single field Joins and Search Criteria. His Schema requires Multiple field joins and Multiple Field searches to drill down to a specific record.

The benefit of his schema is you don't have to make a join when you are looking at a tblOrderLineDetails record to determine the LineNum or the Order ID.

What is your opinion?

Thank you.

both are valid and both have positives negatives.

Personaly I am a strong believer of the meaningless hidden autonumber key on all tables, INCLUDING the Order table.

Furthermore I dont particularly see a use for the OrderLines table, what data would be stored there that is particular to the line but not the article/detail?? I would probably skip that table all together...

namliam said:

Furthermore I dont particularly see a use for the OrderLines table, what data would be stored there that is particular to the line but not the article/detail?? I would probably skip that table all together...

Click to expand...

Agreed. Given the structure shown there is no justification for a separate table. The information held in OrderLinesDetail should simply be a field in the OrderLines table.

Where records in separate table have a one to one correspondence it usually indicates the records belong in the same table.

The only justification for an OrderLinesDetail table would be to hold multiple records related to a single OrderLine record. For example relating an OrderLine to a table of standard comments. One field would be a foreign key to the OrderLine and the other to the Comments table. It would be a Many to Many junction table.

Personally in this case I would relate the junction record directly to an OrderLine PK rather than using a composite key. Consulting one index is faster than two.

The only advantge of the composite key would be retreiving the standard comments related to an order where there was no concern for the particular order line. For example, retreive all the orders that require an item to be drop shipped. But then knowing this without knowing what was actually ordered would seem unlikely.

And I will chime in and say that I will NOT use composite keys (unless I'm forced to - for example by some project I get on that already has done so). I don't like them, I think they are clunky and they are a pain when putting together queries because you have to link on all of the fields that make them up, and you have to store more than one field (redundant data).

Thanks for your response.

I think the three table structure is a wise choice based on the situation. Please comment if you agree or disagree.

Situation: I order 7 apples on line 1 of order A. Assume Composite Key structure:

The first table would hold: Order A, Customer Name, Date, etc...

The second table would hold: Order A, Line 1, 7 Units, Apples, Total Cost: $7.50 ; Order A, Line 2, 4 Units, Peaches, Total Cost: $3.50; etc....

The third table would hold: Order A, Line 1, Item 1, Green Apple, 345g ; Order A, Line 1, Item 2, Red Apple, 390 g etc....


My personal preference is to use Autonumber but this was the way the tables were structured and I wanted to hear other peoples' opinions.

Thanks.

ions said:

The third table would hold: Order A, Line 1, Item 1, Green Apple, 345g ; Order A, Line 1, Item 2, Red Apple, 390 g etc.....

Click to expand...

Disagree.

What would be the point of separating the description and the weight into another table? Those attributes of the order lines could simply be stored as fields in the Order Line table. There is a one to one relationship.

The autonumber vs composite key issue becomes redundant.

(And we talking about the same developer who stores customer name in the invoice lines to avoid the inefficiency of joins?)
http://www.access-programmers.co.uk/forums/showthread.php?p=902392#post902392

Galaxiom.

The relationship between table 1 and 2 is one to many.

The relationship between table 2 and table 3 is one to many.

ions said:

The relationship between table 2 and table 3 is one to many.

Click to expand...

Yes I see this now on closer observation.
I hadn't recognised that there were multiple items on a line and misinterpreted the description as the more typical structure of one item per line - "line" meaning line on the document rather than what I assume is meant, "product line".

Line classification seems a pointless and completely artificial construct. Red Apples are as different from Green Apples as they are from Peaches.

In fact "line" in this context should an attribute of the item and should be stored in a Produce table with other attributes specific to that particular produce. Red apples are and will always be members of the Apple line. This attribute does not change from order to order and hence recording it in each order is a normalisation error.

If the order is to be grouped by line then this should be retreived from the produce table not clumsily grouped by a field (even worse, a table) in the order structure.

Moreover combining the costs and quantities of red and green apples into a single record makes no sense. This structure cannot record the individual cost or quantity of different coloured apples to be supplied. The order could be filled with anything from zero to seven either red or green apples or any combination between provided $7.50 was their total cost.

The issue here is far more fundamental than the table structure. The problem is actually irrational data analysis.

tblOrderLines:
AutoNumberLineID (Just regular Fields: OrderID, LineNum)

igonring discussions about whether this needs 2 or 3 tables, which is just the exercise in normalisation - I disagree that the autonumber needs to be the only key

the autonumber is fine for use as a foreign key. however, you cant just have orderid and linenum as unindexed fields. the problem is that the combination orderid, linenum needs to be unique also (in any normal system) so that you cant have multiple line 1's on a given order.

so the real argument is whether you need the autonumber key AS WELL as the "true" unque key.

its a matter of taste, but at the cost of a bit of storage space, and another key, it probably simplifies relationships between the order lines table, and other tables - and also remve any probelm of cascading updates, should you ever need to change any of this information.

gemma-the-husky said:

so the real argument is whether you need the autonumber key AS WELL as the "true" unque key.

its a matter of taste, but at the cost of a bit of storage space, and another key, it probably simplifies relationships between the order lines table, and other tables - and also remve any probelm of cascading updates, should you ever need to change any of this information.

Click to expand...

Also forgetting the other issues and any potential advantage of being able to relate Table3 directly to Table1, the advantages of composite or single keys are double edged.

The natural composite key definitely has an edge because (as Dave pointed out) it already needs to be unique even if it is not related to tables down the chain. However, relating Table3 via a composite key requires adding two fields to the essential data in Table3.

On the other hand adding an extra PK to Table2 means Table3 would only require one extra field. So the single artifical key is really just one extra field across both tables and not as big an overhead as it might appear.

Adding another table related to the PK of Table2 moves the weight of this argument more heavily toward the artifical key.

A single key does not necessarily simplify cascading updates. For example if a value needs to be updated because it is related to a table whose PK forms half of a composite key, that half is all that is requred to identify the affected records. With the single artificial key it may be necessary to identify the relevant records via another table.

Individual cases should be decided on their merits. I think Bob Larson is a little too strong in his derision of composite keys. Sometimes they are a sensible choice.

I am always suspicious of rules especially when the statements supporting them do not hold up to analysis. Bob stated that a composite key means redundant data. In fact it is quite the opposite. The artificial key could be considered redundant where the relationship can be expressed by a natural composite key.

I am always suspicious of rules especially when the statements supporting them do not hold up to analysis. Bob stated that a composite key means redundant data. In fact it is quite the opposite. The artificial key could be considered redundant where the relationship can be expressed by a natural composite key.

Click to expand...

logically i think this goes further - the artificial key is generally always redundant in terms of the data - I thought one normal form affected data, where rows can be uniquely identified by a field or combination of fields. in those terms the autonumber key is always redundant (am i right ?) - but nevertheless is still useful.

gemma-the-husky said:

... in those terms the autonumber key is always redundant.

Click to expand...

By definition an artificial key is always redundant in terms of real data. It is a database construct used to reconstitute complete records instead of repeating all the fields from the other table across all records.

Conceptually, the join on a key simply represents a "superquery" that rebuilds exactly that in the background.

SELECT [Table1].
[*], [Table2].
[*]
FROM [Table1], [Table2]
WHERE [Table1].[PK] = [Table2].[FK]

Indeed this query can actually be substituted for a join. The only difference between this query and the reality is optimisation.