I have a “comment” table which is the junction table in a many-to-many relationship between the “member” and “article” tables.
Here are my tables…
ARTICLE
id (pk)
slug
title
COMMENT
member_id (pk)(fk)
article_id (pk)(fk)
created_on (pk)(fk)
MEMBER
id (pk)
first_name
Currently I am adding in the ability for people to “Report a Comment” from a given Article page, which necessitates me passing over the “Article ID”, “Member ID”, and “Created On Date” to my “report_comment.php” script.
(For any given Article, the same Member could have posted several Comments, so I need all three pieces of information to know which Comment the person is reporting.)
Simply put, that is a PITA…
And so i am wondering if this might be a good reason to add an auto-increment “comment.id” field, and make that my Primary Key?!
Do so would most certainly make my life easier as far as this script I am working on.
I’m not sure how it might impact other things I might need to do in the future?! :-/
Currently, I can’t think of any places where I am actually using my 3-Key Primary Key other than for ensuring uniqueness in the table…
Still on the “Charge per Word Calling Plan”, I see…
If I create an auto-increment ID for my Primary Key, am I losing anything that the 3 Natural Keys would provide?
Any “gotchas”?!
Oh, and is there any easy way to “lay down” auto-numbers for each record, starting at one for the oldest record and cycling through the rest, or is that a programming project?
i like to distill all the yadda yadda yadda down as much as i can
would you rather i go back to my favourite response… what happened when you tested it???
don’t tell me you’re too lazy to fire up a test case and see what happens
if you do, i’ll say i’m too lazy to write you a long-winded free tutorial every time you ask a database question
if you neglect to declare a UNIQUE key consisting of the same columns you had as the primary key, then yes, and for the same reason you already mentioned – “ensuring uniqueness in the table”
the easiest way is to create a new table, do an INSERT SELECT from the old table, drop the old table, and rename the new one
don’t tell me you’re too lazy to fire up a test case and see what happens
No, I’m smart and I ask here before I go break things, then once I know I am going down the correct path, then I go off and build and test…
if you neglect to declare a UNIQUE key consisting of the same columns you had as the primary key, then yes, and for the same reason you already mentioned – “ensuring uniqueness in the table”
Does having a “Surrogate Key” (generated) and a “Natural Key(s)” (physical) weigh down your table and queries?
Seems like doing that whenever possible would be the “best of both worlds”, right?
the easiest way is to create a new table, do an INSERT SELECT from the old table, drop the old table, and rename the new one
It looks like it worked, but I am unsure of myself…
Again, I’m looking for a little “You are going about this the right way.” (Or, “That will blow up your database?!”)
srsly, i can’t believe you set that up so easily
If everything was easy, I wouldn’t come here for help…
Debbie
P.S. r937, you might want to factor in that my code-base is probably over 10,000 lines of code now, and of that, I have slaved and written at least 8,000 lines of that code entirely myself. The other 2,000 was from nice, generous, and smarter-than-Debbie people like you here on SitePoint. Try cutting me some slack…
well, sort of… but so marginally that you probably couldn’t measure the difference, so it’s certainly a viable strategy
correct
this is actually advocated by many database developers, and i agree with it, but only to the point where the surrogate key has a reason for being, rather than just being rote, having one in every table
yours was one of the good reasons for having a surrogate pk – a child table that has to reference this table, but this table has a three-column natural key
So until I get a database with 1,000,000 Users in it, I am probably good, right?!
this is actually advocated by many database developers, and i agree with it, but only to the point where the surrogate key has a reason for being, rather than just being rote, having one in every table
And what would be some “rote” examples?
Here are some design decisions I made long ago (but they might need to be tweaked)…
ARTICLE
id (pk)
slug (uk)
title (uk)
MEMBER
id (pk)
email (uk)
username (uk)
PRIVATE MESSAGE
id (pk) **nothing to physically make certain every PM is unique, but I don’t think you want to do that?!