Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

If I need a table to hold Point of Sales transactions, and am told I need to store:

Country ID
Store Number
POS Terminal Number
Transaction Date
Item Code
Teller ID
Another Field
More Fields

Now, in this case, the uniqueness would be:

Country ID, Store Number, POS Terminal Number, Transaction Date, Item Code

I am always unsure if it's best to have a identity colmn as the primary key - in this case, maybe TransactionID INT NOT NULL PRIMARY KEY, and then a unique constraint across the unique fields.

Or, should a primary key be created across all the unique fields?

The benefit of the TransactionId I guess would be, joins. To join back to a transaction, you just use the single field. I can't see the benefit of the PK across a number of fields. (Save space of an extra column?).

share|improve this question

migrated from stackoverflow.com Mar 22 '13 at 8:36

This question came from our site for professional and enthusiast programmers.

1  
I often wonder about this "religious debate" as well. I find developers prefer identities and dbas tend to prefer composite keys. Either way, here are some good replies: stackoverflow.com/questions/350950/multi-column-primary-keys –  sgeddes Mar 22 '13 at 4:48
    
Just ask yourself this: if a child table needs to reference this table here - is it easier to have a join condition on a single identity column, or always have to join on five columns that make up your primary key? –  marc_s Mar 22 '13 at 5:56
    
Totally agree, @marc_s. I'm just wondering why I am seeing it so much on a project I am assigned to at the moment. Surely an IDENTITY, along with a Unique Constraint would be better, both for development and maintenance, as well as efficiency? –  Craig Mar 22 '13 at 6:01
1  
@Craig: I see the origin of the "religious debate" in that the DBA's are attempting to understnad the Conceptual Model from the Physical, and the developers are attempting to efficiently use the Physical Model from the Conceptual. Bot are right, but they are discussing separate domains with (unfortunately) shared terminology. –  Pieter Geerkens Mar 22 '13 at 8:14
1  
Re identity property: If you use the identity property of a table to automatically generate your surrogate key then you lose the ability to do any multi-master operations. In other words, all your terminals in all of your stores in all of your countries must talk to one and only one SQL Server instance. If you want multi-master operation now or think you might want it in the future then don't use identity tables. Either Programmatically create a surrogate key or don't use a surrogate key. –  Greenstone Walker Mar 25 '13 at 4:37
show 1 more comment

3 Answers 3

I would say yes, create the surrogate key of TransactionID. It would create a narrow, unique identifier across the table.

One of the reasons why a surrogate key would be best is because of relationships with other tables. If you need to relate the Transaction table with another table (Line_Item_Detail for instance) the entire primary key of the Transaction table would need to be in the related table as a foreign key. If you decided to use your candidate key of Country ID, Store Number, POS Terminal Number, Transaction Date, and Item Code these columns would need to be in every related table. If you would need to update any of these fields they would need to be updated in the related tables too. This gets messy very quickly. With a surrogate key of TransactionID you would only need to add the TransactionID column to your related table. Since this will, should be, and IDENTITY column we should never have to update it.

Another thing you should be thinking about when creating SQL Server tables is the clustered index. A clustered index is how SQL Server physically stores the data for a table. You can have a table without a clustered index called a heap. In most cases you really should create a clustered index (read more about heaps vs. clustered indexes) Michelle Ufford has an excellent post on creating Effective Clustered Indexes. In short, your clustered indexes should be:

  • Narrow – as narrow as possible, in terms of the number of bytes it stores
  • Unique – to avoid the need for SQL Server to add a "uniqueifier" to duplicate key values
  • Static – ideally, never updated
  • Ever-increasing – to avoid fragmentation and improve write performance

A clustered index on TransactionID would fill this criteria nicely.

Since you'll be adding a surrogate key to the Transaction table you should also consider adding an alternate key of Country ID, Store Number, POS Terminal Number, Transaction Date, and Item Code or some other candidate key. This will guarantee there will be no accidental duplicates added to the table. If you don't add the alternate key there is a strong probability that duplicates will creep into the table.

One last thing, you may want to consider normalizing the table further. With the table design your provided if more than one item is added to a transaction you will be adding duplicate data to every row (Store Number, POS Terminal Number, etc). Querying will also become more difficult due to the duplication of data.

share|improve this answer
add comment

It sort of depends on your queries, If you make a lot of queries on just, say, the TransactionId, it would be more natural to make that the primary key. It'll also make it easier for the DBMS to create an index over just that one field. You should still, of course, specify a unique constraint over all the columns you mentioned.

If, on the other hand, you do all your look up queries by specifying all the fields, I would make them all a composite primary key.

share|improve this answer
add comment

I am having trouble following your train of thought... The 5 things you listed should not indicate any kind of uniqueness at all. There can only be 1 item sold a day at a location?

Create the TransactionId as you suggested.

Also this is a great resource for table normalization http://www.informationqualitysolutions.com/FreeStuff/rettigNormalizationPoster.pdf (ie. properly designing tables)


EDIT: **********


Craig your assumption is incorrect. Look at the SQL documentation for the DATE type:

DATE - This describes a date using the fields YEAR, MONTH and DAY in the format YYYY-MM-DD. The length is 10. (ie. it doesn't include hours minutes, seconds, etc.)

Only 1 item can be sold a day the way you wrote the original question.

This is exactly why a primary key should be separate of all other information. Also we can't predict if any other kind of uniqueness will hold in the future (eg. even if you use datetime its possible in the future that a POS terminal could become an online server in which 2 transactions could technically occur at the same DATETIME)

share|improve this answer
    
No, a single item, can only be sold once, at a store, at a specific point in time, on a specific terminal. An item can be sold as much as possible in the day, but only ones at that DATETIME, on that terminal in that shop. –  Craig Mar 22 '13 at 4:56
    
If you're using a DATETIME you may want to consider using DATETIME2. DATETIME has a precision of .000 seconds and rounds the value to .000 or .003 or .007. For a POS the exact time matters in most cases. DATETIME2 has precision to .0000000 seconds. –  Jorriss Mar 22 '13 at 14:49
2  
Craig, your question states "Transaction Date", thus Ian's comment. If you mean to include time as well then you should edit it to say "Transaction Datetime". –  Greenstone Walker Mar 25 '13 at 4:41
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.