Order Detail Table Primary Key

[dcavaiani]

Curious what/why anyone has an order detail table with a primary key(s) ??

 

[Uncle Gizmo]

Well let's say you sent out the order to a customer and the customer returned one of the items. You might find an individual ID useful in this situation.

 

[jdraw]

This may help:

Primary Key
Definition - What does Primary Key mean?


A primary key is a special relational database table 
column (or combination of columns) designated to 
uniquely identify all table records.

A primary key’s main features are:

    It must contain a unique value for each row of data.
    It cannot contain null values.

A primary key is either an existing table column or a column
 that  is specifically generated by the database according
 to a defined sequence.

[B]Techopedia[/B] explains Primary Key

The primary key concept is critical to an efficient relational database.
 Without primary key and closely-related foreign key concepts, relational databases would not work.

Almost all individuals deal with primary keys frequently but unknowingly in everyday life.
 For example, students are routinely assigned unique identification (ID) numbers,
 and all adults receive government-assigned and uniquely-identifiable Social Security numbers.

For example, a database must hold all of the data stored by a commercial bank.
 Two of the database tables include the CUSTOMER_MASTER, which stores basic 
 and static customer data (e.g., name, date of birth, address and Social Security number, etc.)
 and the ACCOUNTS_MASTER, which stores various bank account data (e.g., account creation date,
 account type, withdrawal limits or corresponding account information, etc.).

To uniquely identify customers, a column or combination of columns is selected to guarantee
 that two customers never have the same unique value. Thus, certain columns are immediately
 eliminated, e.g., surname and date of birth. A good primary key candidate is the column that
 is designated to hold unique and government-assigned Social Security numbers. However, some
 account holders (e.g., children) may not have Social Security numbers, and this column’s candidacy
 is eliminated. The next logical option is to use a combination of columns such as the surname to
 the date of birth to the email address, resulting in a long and cumbersome primary key.


The best option is to create a separate primary key in a new column named CUSTOMER_ID. 
Then, the database automatically generates a unique number each time a customer is added,
 guaranteeing unique identification. As this key is created, the column is designated as
 the primary key within the SQL script that creates the table, 
 and all null values are automatically rejected.

The account number associated with each CUSTOMER_ID allows for the secure handling
 of customer complaints or queries and also demonstrates why primary keys offer the fastest
 method of data searching within tables. For example, a customer may be asked to provide his
 surname when conducting a bank query. A common surname (e.g., Smith) query is likely to return
 multiple results.
 When querying data, utilizing the primary key uniqueness feature guarantees one result.

 

[dcavaiani]

It makes sense that you would want a direct access to one of the detail order records for say a refund.

 

[Uncle Gizmo]

You could do it without a primary key. You would use the invoice number and a line item number.

The line item number for each invoice would run from 1 to however many line items there were and would be indexed unique on the two fields:- invoice number & line item number.

However it's easier to use a single unique index on all the line items.

 

[ButtonMoon]

Curious what/why anyone has an order detail table with a primary key(s) ??

The simple answer is data integrity. Let me turn the question around: Why would you want an order detail table that allows duplicate data? Duplicate data means redundancy, ambiguity and potentially incorrect results from your queries. These are some of the reasons why sensible database designs generally use keys in all tables.

 

[ButtonMoon]

You could do it without a primary key. You would use the invoice number and a line item number.

That seems like a contradiction to me. Wouldn't invoice number and line item number be the primary key in that case?

 

[Uncle Gizmo]

That seems like a contradiction to me. Wouldn't invoice number and line item number be the primary key in that case?

Yes! Well spotted!

 

[Uncle Gizmo]

You don't necessarily need a line item number you could use the invoice number and the product ID. However you could have a situation where, let's say a Plumber brought 3 tap washers,, the clerk entered the 3, but then the plumber decided he wanted 3 more. The clerk who had already entered 3, now proceeds to enter three more on the next line. A perfectly legitimate and understandable situation.

A couple of days later the plumber decides he didn't need the other 3 tap washers and returns them to the store. The clerk opens up the invoice and tries to credit one of the line items, but as there are two rows both identical, there is no way for the program to choose between them.

To prevent this situation occurring you impose a unique index on the invoice number and the product ID. The same situation occurs again with another plumber. The plumber buys 3 tap washers, then adds 3 more, to the order. However the clerk is unable to add a new line item because they unique index prevents the addition.

You would need to add code which would tell the clerk to update the existing three items to 6 items, a bit more coding, and probably not the best solution for the clerk.

 

[dcavaiani]

The simple answer is data integrity. Let me turn the question around: Why would you want an order detail table that allows duplicate data? Duplicate data means redundancy, ambiguity and potentially incorrect results from your queries. These are some of the reasons why sensible database designs generally use keys in all tables.

How would a random number primary key prevent duplicates in the substance of the detail line ?

 

[jdraw]

Why would you have a table in a relational database without a Primary Key?

1 Order may have 1 or Many LineItems/Products/Services
and
1 LineItem/Product/Service may occur on 0,1 or many Orders
So Orders to Products is a Many To Many relationship.

The M:M is resolved by using 2 1:M relationships and a new junction table. Here it is OrderDetail.

In OrderDetail, typically, you store the details/lineitems/products/services
that were part of the Order. Order to OrderDetail is a 1:Many relationship.
LineItem/Product/Service to OrderDetail is 1 to Many.

There are usually 2 options/approaches to identifying the PK of a junction table(orderDetail).
-You can use the PK of each of the original tables as a composite Primary Key.
-or, you could have an autonumber (surrogate) (your random number --but in reality it is simply a unique number). If you choose option 2, then to prevent duplicates in OrderId and Productid, you create a unique composite index of the required fields. The database system will not allow duplicates or NULLs in the unique composite index.

If you use a unique random number as PK of OrderDetail, the database system will still not allow duplicates in your chosen Primary Key. But users typically don't know the unique random number and often work with OrderID and ProductID or ProductName --hence the unique composite index.

You seem to have some reluctance to using a primary key or are questioning why you would have one.

As Buttonmoon said --it's about data integrity.

 

[ButtonMoon]

How would a random number primary key prevent duplicates in the substance of the detail line ?

Clearly it wouldn't. The substance of the detail line (meaningful attributes) ought to be the key.

 

[gemma-the-husky]

Whether you can do it without a PK is a moot point, technically

I thought Access (and other databases) mandated a PK to be available in order for particular operations, such as update operations to be available.

 

[dcavaiani]

Whether you can do it without a PK is a moot point, technically

I thought Access (and other databases) mandated a PK to be available in order for particular operations, such as update operations to be available.

I am in Agreement!

 

[AccessBlaster]

How would a random number primary key prevent duplicates in the substance of the detail line ?

It's not the random number that prevents the dupes, its the unique non repeating number that guarantees no dupes.

 

[dcavaiani]

It's not the random number that prevents the dupes, its the unique non repeating number that guarantees no dupes.

Which accomplishes NOTHING!

 

[ButtonMoon]

Which accomplishes NOTHING!

Agreed. But your original question was why have a key for an order detail table. An order detail table should have a key for the same reason as any other table: data integrity. For sure a "random", meaningless or poorly chosen key probably won't achieve much and is probably no better than having no key at all. The requirement for a key still remains: you need sensible, usable, well-chosen keys in your order detail table.

 

[gemma-the-husky]

as always there are two different things, (IMO)

(Note that I use the terms index/key interchangeably - the PK is just a designated unique index )

one is say, the index/key that makes the record unique and identifiable. This could be an autonumber, or an index consisting of two fields, order number and order line number.

A completely different thing (although it could be the same) is the index that manages the data integrity. Let's say the business rule is that you cannot have the same product code on two different lines of the same order.

Therefore you need one of two things.
a) an index combining OrderNumber and ProductCode (which could be a candidate PK)
or
b) code that checks to see whether this combination is present before allowing a duplicate to be added.

I suggest that we would all add the business-rules/integrity index. Some would use it as a PK, others may not. I probably wouldn't. I often prefer not to use the business rules key in managing relationships. I certainly prefer a single field numeric key.

Two different things though. PK for relationships. Business key for data integrity. Lots of the discussions about autonumber PKs seem to ignore the need for management of business-rules.

 

[dcavaiani]

You don't necessarily need a line item number you could use the invoice number and the product ID. However you could have a situation where, let's say a Plumber brought 3 tap washers,, the clerk entered the 3, but then the plumber decided he wanted 3 more. The clerk who had already entered 3, now proceeds to enter three more on the next line. A perfectly legitimate and understandable situation.

A couple of days later the plumber decides he didn't need the other 3 tap washers and returns them to the store. The clerk opens up the invoice and tries to credit one of the line items, but as there are two rows both identical, there is no way for the program to choose between them.

To prevent this situation occurring you impose a unique index on the invoice number and the product ID. The same situation occurs again with another plumber. The plumber buys 3 tap washers, then adds 3 more, to the order. However the clerk is unable to add a new line item because they unique index prevents the addition.

You would need to add code which would tell the clerk to update the existing three items to 6 items, a bit more coding, and probably not the best solution for the clerk.

My app was actually like this too. I have the Header keyed by a RecordID, and I have just added a composite Primary Key on the Detail using the RecordID, the Customer, and the ProductID. So, my app would work just like the above example where there could now NOT BE two Detail lines with the same Customer and ProductID.

The app is used for a Contractor to enter in all the Material Purchases they make based on the 'slip' or 'receipt' they would get from say Home Depot. The Customer would have to be 'identified' by the Contractor at time of data entry, and they COULD be more than ONE Customer that the Contractor has purchased material for on this same 'slip'. I do have the Customer (combo box) copying from the previous added line.

Example:

Header: Supplier, Invoice/Slip#, Date, RecordID
Detail: Customer, ProductID, QTY, Price, etc., RecordID

Thank you for some FINE input and examples from ALL respondents!

 

[gemma-the-husky]

My app was actually like this too. I have the Header keyed by a RecordID, and I have just added a composite Primary Key on the Detail using the RecordID, the Customer, and the ProductID. So, my app would work just like the above example where there could now NOT BE two Detail lines with the same Customer and ProductID.

The app is used for a Contractor to enter in all the Material Purchases they make based on the 'slip' or 'receipt' they would get from say Home Depot. The Customer would have to be 'identified' by the Contractor at time of data entry, and they COULD be more than ONE Customer that the Contractor has purchased material for on this same 'slip'. I do have the Customer (combo box) copying from the previous added line.

Example:

Header: Supplier, Invoice/Slip#, Date, RecordID
Detail: Customer, ProductID, QTY, Price, etc., RecordID

Thank you for some FINE input and examples from ALL respondents!

BIB: actually this won't work. by adding the recordID to the composite key, you actually allow duplicate product codes, since the recorded will always be different. The customer shouldn't be part of the PK either. A customer may have multiple orders.

it really HAS to be

order table: ordernumber ....
order detail table: order number, orderline (or product code)