November 28, 2008 at 10:48 pm
One senerio: Library - Employee (EmpID)- Books(BookID), Here employees are eligible to take and return books for every day, or every week or every month, and soon
In this transaction table "EmpID", and "BookID" are FK'S (There are some other columns AssignedOn, ReturnedOn, .....)
In this case which one is best one to create PRIMARY KEY for this Transaction table
CASE 1: EmpID, BookID - together Composite Primary Key
CASE 2: EmpID,BookID,AsssignedOn - Together Composite Primary Key
CASE 3: Creating one new column "AutoID" with autogenerated number and set it as Primary Key
Which one is best and why?
November 29, 2008 at 9:53 am
It depends on what you want to do. My personal preference is to use an identity column as the primary key which I use in relationships and for updates and deletes and make the EmpID, BookID natural key combination as a unique constraint/index. Others will have other opinions.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
November 29, 2008 at 10:06 am
this may also give you useful information...
http://www.sqlservercentral.com/Forums/Topic608554-361-1.aspx
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
November 29, 2008 at 11:18 am
sriniw8z (11/28/2008)
In this case which one is best one to create PRIMARY KEY for this Transaction table
As said, it depends on what you want to do:
CASE 1: EmpID, BookID - together Composite Primary Key
This allows you to track who currently has what book out. It cannot be used for historical queries, like "Select all employees who checked out this book last year", because it cannot accomodate the same employee checking out the same book at two different times.
CASE 2: EmpID,BookID,AsssignedOn - Together Composite Primary Key
This makes a larger key, but allows proper historical records and queries.
CASE 3: Creating one new column "AutoID" with autogenerated number and set it as Primary Key
This is only useful, relationally, if you plan to have rows in other tables referring to these rows. That doesn't seem likely, so I wouldn't recommend it.
The other thing that a key like this can be used for, is so that you do not have to put any Unique constraints on the rest of the columns. Then you can just pile in the rows without regard to PK violations. While this has its uses ((a) very early prototyping, (b) personal "grunge" development, (c) No candidate PK columns), you really should not use this approach unless you have to.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
December 1, 2008 at 6:54 am
As everyone else has said, it depends.
I used to use the approach of an identity column on most, if not all, tables. I've learned better. Now I tend towards what you've defined as either option 1 or option 2. Major objects have an identity, like you have with employee and book. After that, the minor objects are usually built from relationships between larger objects so they get compound keys. Whether or not you use option 1 or 2, Barry has already defined. A one time relationship, option 1, multiple times, option 2, but you might also want to add another column to the table, not part the key, for the end date or check-in date, whatever you're calling it.
I'd also add, when building these compound keys, make them the clustered index for the table. Most, if not all, data access will be through the keys, so that makes a prime candidate for the cluster. Otherwise, you're likely to get a lot of bookmark lookups.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply