Argh Bad Table Design!!!!!@$dfasf !!! Rant !!!

By smith | July 4, 2009

  • What is wrong with this table:


    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]


  • Basicly,

    Use the AUTO INCREMENT field to easily uniquely identify a row if you're going to show it to the user from the web end -- e.g. posts need it, threads do, etc.

    Look at the session table -- there's an AUTO INCREMENT column on there. WHY? The application NEVER queries (at least in 1.X) the row based on this ID -- it's basically wasting CPU time (to generate the new ID each insert) and database space.

    If you're using a table to JOIN with -- say a poll vote table:
    pollvote( pollvoteid auto increment, userid, pollid, choice );

    the pollvoteid auto increment field is 100% useless and should not be there. You JOIN the poll vote and the poll table on the pollid, NOT the pollvoteid.

    It just comes down to basic design issues -- it's BAD design to use Auto Increment columns on tables that do not need it -- likewise it's silly to set the PRIMARY KEY to this column EACH time if you don't need to.

    For the users table, a more appropriate PRIMARY KEY would be the user name, since that is what the application does any way.

    For the post table, you really don't need a primary key, a UNIQUE INDEX on the postid would suffice.

    Kind of see what I'm getting at?

    (btw spellchecker.net is down so there could be misspellings ;))


  • *** student sits down for the lecture ***

    shame i i have no clue what you're saying but i'll get there :D







    • #If you have any other info about this subject , Please add it free.#
    Your name:
    E-mail:
    Telphone:

    Your comments:


    If you have any other info about Argh Bad Table Design!!!!!@$dfasf !!! Rant !!! , Please add it free.

    Topics: enart.webtony.com | edit

    Global.php ???? Too many connections Error