Re-thinking my Primary Key

[DoubleDee]

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...

Thoughts??

Thanks,


Debbie

[r937]

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?!

yes

[DoubleDee]

yes

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?


Debbie

[r937]

Still on the "Charge per Word Calling Plan", I see...

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 I create an auto-increment ID for my Primary Key, am I losing anything that the 3 Natural Keys would provide?

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"

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?

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

[DoubleDee]

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???

Not really...

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

Okay.

Thanks,



Debbie

[DoubleDee]

How do I make an exact copy of the STRUCTURE of my "comment" table into a new table called "comment"backup"?

Will this do that...

CREATE TABLE comment_backup LIKE comment;

(And I mean, same Fields, Data-Types, Attributes, Index, eeverything!)


Debbie

[r937]

Will this do that...

i was gonna try and wait 24 hours before replying, but i didn't want to let someone else jump in and say this ....

what happened when you tested it?



srsly, i can't believe you set that up so easily

[DoubleDee]

i was gonna try and wait 24 hours before replying, but i didn't want to let someone else jump in and say this ....

what happened when you tested it?

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...

[r937]

Does having a "Surrogate Key" (generated) and a "Natural Key(s)" (physical) weigh down your table and queries?

well, sort of... but so marginally that you probably couldn't measure the difference, so it's certainly a viable strategy

Seems like doing that whenever possible would be the "best of both worlds", right?

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

[DoubleDee]

well, sort of... but so marginally that you probably couldn't measure the difference, so it's certainly a viable strategy

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?!


And of course...

COMMENT (current)
-------------
- article_id (pk)
- member_id (pk)
- created_on (pk)


COMMENT (revised)
-------------
- id (pk)
- article_id (uk)
- member_id (uk)
- created_on (uk)


Thoughts on these?

Thanks,


Debbie

[r937]

Thoughts on these?

nope

you've exhausted your three free tutorials for today, sorry

someone else, i am sure, will be happy to pick up the torch

here they come now...