SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    What is wrong with this table:

    Code:
    CREATE TABLE rating
      rating_id int unsigned not null auto_increment,
      user_id int unsigned not null,
      table_id int unsigned not null
     PRIMARY KEY( rating_id );
    This table is used to store the fact that a user, referenced by user_id, has "voted" for a particular table referenced by table_id.

    Ok, there are TWO things that I can count as BAD CRAPPY HORRIBLE NO-NOs in this design.

    1) The auto_increment column is pretty useless
    2) The primary key is set to the auto_increment but SHOULD BE user_id, table_id

    Moral of the story?

    Use the auto_increment column WHERE NECESSARY -- for crying out loud every table you design DOES NOT NEED AN AUTO INCREMENT COLUMN ATTACHED TO IT!

    ALSO -- the PRIMARY KEY constraint is used to... well, VERIFY the UNIQUE IDENTIFIER for a row. In the above example you can easily insert:
    ( 1, 2 ) as many times as you want, even if your application requires ONLY ONE VOTE PER PERSON, PER TABLE. As-is you will end up with an inconsistent database.

    In conclusion:
    DO NOT ADD AUTO_INCREMENT COLUMNS TO EVERY DAMN TABLE YOU CREATE
    CREATE PRIMARY KEYS THAT MAKE SENSE

    [/rant]

  2. #2
    Grumpy Mole Man Skunk's Avatar
    Join Date
    Jan 2001
    Location
    Lawrence, Kansas
    Posts
    2,067
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Great tip - I had to admit your code example is exactly what I would have done. I'll bare that in mind in the future

  3. #3
    Bimbo With A Brain! silver trophy Saz's Avatar
    Join Date
    Mar 2001
    Location
    Kent, United Kingdom
    Posts
    5,275
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well you've totally lost me!!

    I'm ok with the "What is wrong with this table" bit, but every thing after that..........WHOOOOOOOSH (straight over my head!)
    Saz: Naturally Blonde, Naturally Dizzy!
    No longer Editor of the Community Crier.

    Don't mind me, I'm having a BLONDE moment!

  4. #4
    Hi there! Owen's Avatar
    Join Date
    Jan 2000
    Location
    CA
    Posts
    1,165
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well how about this?

    lets assume that you allow users (optionally annonymously) to write a review. then at the bottom of every review you have a link saying "Report a violation" (ie fradulous review, or whatever). How could you get a link to the specific review without having a unique identifier? Auto-incriment seems like the easiest way here. I agree about not being the primary key, but in this case it could be useful. Right? If you allow annonymous submissions (like many sites do) then you can't make any column except review id primary.

    I don't know... i'm runing into this EXACT same issue in a db i'm making right now...

    Owen

  5. #5
    Grumpy Mole Man Skunk's Avatar
    Join Date
    Jan 2001
    Location
    Lawrence, Kansas
    Posts
    2,067
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Owen - you're right in most cases about the auto incremented field bing essential for providing a unique identifier, but in the example above no unique ID is needed as the table is being used to relate two other tables to each other. If you wanted to delete the relationship you could do it by deleting the entry where user_id and table_id are known values.

  6. #6
    Hi there! Owen's Avatar
    Join Date
    Jan 2000
    Location
    CA
    Posts
    1,165
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Okay, as long as we agree about that.

    Owen

  7. #7
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Correct,

    I never said you shouldn't EVER use it, but a certain application I'm looking at right now has it in EVERY table and in most cases it is NEVER USED.

    That is wasting space and time -- e.g.
    an integer value on EACH row
    time for the database management system to figure out what the next one is on each insert
    and just bad bad bad.

    They are useful if you are going to be doing something like that -- if there is no other way to identify a row then sure go ahead and use it.

  8. #8
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Most of the time an AUTO INCREMENT is unnecessary unless there is no other way to uniquely identify a table. As Skunk said, you can just "DELETE table WHERE someid = 4 AND someotherid = 5".

    My whole point is that it seems like everyone and their mother are following this table creation convention:
    Code:
    CREATE TABLE bob AS(
      bobID INTEGER AUTO INCREMENT NOT NULL,
      ...
      ...
      ...
      PRIMARY KEY( bobID ) 
    );
    And that's just BAD and comes out of ignorance for "good" table design basically.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •