Primary Keys are necessary for good database design

Question

Primary Keys are necessary for good database design

Viewing 8 posts - 16 through 22 (of 22 total)

You must be logged in to reply to this topic. Login to reply

SteveBell SSCrazy Points: 2008 More actions · Answer 1

Jeff Moden (12/30/2011)
IMHO, Kimberly Tripp gives the most compelling reason to use an Integer-based PK and why it should usually be the the Clustered Index... performance. Please see the "movie" at the following URL. It's worth every minute.
http://technet.microsoft.com/en-us/sqlserver/Video/gg508879
And, yeah... there's nothing wrong with having a surrogate key as the PK and a natural key as an AK. 😉

Thanks for the link Jeff... it IS worth every minute.

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 2

Thanks for the feedback, Steve. I was absolutely amazed at how she turned such a potentially dry subject into something so interesting and useful.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

cw.izatt SSC Eights! Points: 856 More actions · Answer 3

I will give a really good example: we recently installed imaging software from one of our vendors and when i was trying to check to make sure all of our data was converted correctly I found out there is not one single primary key in any table. Who the hell does that? So here is the problem they decided to be real a$$holes and won't give me a data map and because there are no keys i can't map anything with Visio, so my only other option is to sit and look at traces and figure out how they are using the 400+ tables PITA.

Why would a vendor even do that to themselves?

Never stop learning or you will be left behind.

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 4

@cw.izatt ,

Did you actually check for an absence of FKs or just an absence of PKs? I ask because UNIQUE constraints will suffice for the instantiation of DRI. DRI doesn't have to be based on PKs... just UNIQUE constraints.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

cw.izatt SSC Eights! Points: 856 More actions · Answer 5

Jeff Moden (1/9/2012)
@cw.izatt ,
Did you actually check for an absence of FKs or just an absence of PKs? I ask because UNIQUE constraints will suffice for the instantiation of DRI. DRI doesn't have to be based on PKs... just UNIQUE constraints.

I check for both and i am sure that is why when i ask for a data map they do not have one.

Never stop learning or you will be left behind.

Jared SSC Guru Points: 61793 More actions · Answer 6

cw.izatt (2/13/2012)
Jeff Moden (1/9/2012)
@cw.izatt ,
Did you actually check for an absence of FKs or just an absence of PKs? I ask because UNIQUE constraints will suffice for the instantiation of DRI. DRI doesn't have to be based on PKs... just UNIQUE constraints.
I check for both and i am sure that is why when i ask for a data map they do not have one.

To be honest, and I am not joking, I love reverse engineering databases! Its like a big puzzle 🙂 Think of it as a contest or something. So, this IS a relational database right? If it truly is, there has to be some way to link these tables together. As long as I am getting paid for it, I make a game out of it. That and I pride myself on reverse engineering large databases.

EDIT: And 9 times out of 10 the key column is labeled id or something, or is not necessarily an indicator of the relationship between tableA and tableB. So looking for the existence of primary keys can sometimes be a waste of time anyway.

Jared
CE - Microsoft

Possinator Hall of Fame Points: 3422 More actions · Answer 7

I love reverse engineering databases! Its like a big puzzle

It's perverse but I'm so with you on this. I think it appeals to my need for bringing order out of chaos. As much as I whine, I secretly enjoy it. (Especially when no one else can figger it out) 😛

[font="Courier New"]Looking for a Deadlock Victim Support Group..[/font]

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 8

SQLKnowItAll (2/13/2012)
cw.izatt (2/13/2012)
Jeff Moden (1/9/2012)
@cw.izatt ,
Did you actually check for an absence of FKs or just an absence of PKs? I ask because UNIQUE constraints will suffice for the instantiation of DRI. DRI doesn't have to be based on PKs... just UNIQUE constraints.
I check for both and i am sure that is why when i ask for a data map they do not have one.
To be honest, and I am not joking, I love reverse engineering databases! Its like a big puzzle 🙂 Think of it as a contest or something. So, this IS a relational database right? If it truly is, there has to be some way to link these tables together. As long as I am getting paid for it, I make a game out of it. That and I pride myself on reverse engineering large databases.
EDIT: And 9 times out of 10 the key column is labeled id or something, or is not necessarily an indicator of the relationship between tableA and tableB. So looking for the existence of primary keys can sometimes be a waste of time anyway.

I USED to like stuff like that. Then, a job spoiled me. I was the one that had to design a database from the ground up. I didn't miss having to figure out someone else's change of column names 7 times for the same data. I didn't miss having to figure out how each table related to another. I didn't miss not having the right kind of FK's. I didn't miss not having each column documented in extended properties. And I didn't miss trying to figure out what a hard coded "2" meant. And I sure didn't miss having to type underscores or read all upper or lower case column and table names nor Hungarian notation. 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)