Re-thinking my Primary Key

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

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

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

:smiley:

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

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

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

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?

:smiley: :smiley:

srsly, i can’t believe you set that up so easily :wink:

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 :wink:

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

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

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…