WEB Database setup

Discussion in 'OT Technology' started by hurleyint1386, Jan 18, 2009.

  1. hurleyint1386

    hurleyint1386 Someone has sand in their vagina

    Joined:
    Jan 6, 2005
    Messages:
    3,687
    Likes Received:
    0
    Location:
    Rochester, NY
    So I'm designing a database with some information. Here's an example of an output:

    Code:
    id  |  col1  |  col2   |  col3    | col4     |
    ----------------------------------------------
    1   |   x    |    y    |     z    |     a    |
    2   |   x    |    y    |     c    |     e    |
    3   |   x    |    y    |     f    |     e    |
    4   |   g    |    s    |     f    |     a    |
    
    I vaguely remember reading somewhere that if you've got a column that has a lot of similar results, you should set up a table with all the options for that column, then refer to that table from the other table via an index id by joining the tables. If you have 4 columns set up that way, joining 4 tables in a sql statement could get quite lengthy. Am I off base here? Or would it be best to just have one table and repeat things over ad over in a column? If this doesn't make sense, I'll try to clarify anything you might be confused about. Thanks a lot.
     
  2. 95vr4

    95vr4 OT Supporter

    Joined:
    Oct 6, 2004
    Messages:
    2,513
    Likes Received:
    0
    Location:
    Weddington, NC
    What you're referring to is called normalization (just google it). A bunch of random letters doesn't tell you anything (other than 1NF maybe), post some actual sample data
     
  3. hurleyint1386

    hurleyint1386 Someone has sand in their vagina

    Joined:
    Jan 6, 2005
    Messages:
    3,687
    Likes Received:
    0
    Location:
    Rochester, NY
    Normalization sounds familiar. Some sample data would be an inventory of books. You've got a column for author, title, and isbn for id. Lets say you've got hundreds of thousands of books, some with the same title, and some with the same author.
     
  4. 95vr4

    95vr4 OT Supporter

    Joined:
    Oct 6, 2004
    Messages:
    2,513
    Likes Received:
    0
    Location:
    Weddington, NC
    definitely want to make a separate authors table.
    The titles are dependant solely on the ISBN (prim key), so you should probably be fine as is with them.
     
  5. hurleyint1386

    hurleyint1386 Someone has sand in their vagina

    Joined:
    Jan 6, 2005
    Messages:
    3,687
    Likes Received:
    0
    Location:
    Rochester, NY
    What about if I wanted to do a computer inventory database with motherboard, ram, cpu, hard drive, etc? would I do all different tables for each piece of hardware? or do one large table? if i do multiple tables, how do I join them easily? I remember joining three tables and it being a bitch to do. I can't imagine doing more than that
     
  6. Ender0910

    Ender0910 woot!

    Joined:
    Jun 1, 2004
    Messages:
    3,039
    Likes Received:
    0
    Location:
    Redmond/Bay Area
    If you have a set number of pieces of hardware, it's possible for you to make a table for each, but it may still be a pain to do and deal with.

    Or you can normalize the hell out of it and do something like:

    1. "Item" table - ItemID, ItemName, ItemTypeID (ex. 1, 'ATI x1900', 5)
    2. "Item Type Enumeration" table - ItemTypeID, ItemTypeName (ex. 5, 'Video Card')
    3. "Stats" table - StatID, ItemID, StatTypeID, Value (ex. 3, 1, 2, '512mb')
    4. "Stats Type Enumeration" table - StatTypeID, StatName (ex. 2, 'Video RAM')


    That way every "Item" can be identified, and given an unlimited amount of different stats, etc.
     
  7. 95vr4

    95vr4 OT Supporter

    Joined:
    Oct 6, 2004
    Messages:
    2,513
    Likes Received:
    0
    Location:
    Weddington, NC
    Would each component be identified uniquely (like an asset tag)?

    Read this, it explains it much better than I could
    http://dev.mysql.com/tech-resources/articles/intro-to-normalization.html
     
  8. hurleyint1386

    hurleyint1386 Someone has sand in their vagina

    Joined:
    Jan 6, 2005
    Messages:
    3,687
    Likes Received:
    0
    Location:
    Rochester, NY

Share This Page