#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2013
    Posts
    2
    Rep Power
    0

    How to design my table


    Hello,

    I have a question about a table design:

    I have the following tabel:
    Components with following column
    ID - int
    Name - varchar

    this table has probably 300-400 different records (components).
    I need to design another table "Magic potion" that should contain different components from 0-20 each component in different quantity

    Magic potion1 with following column
    Components_ID - val_1(decimal)
    Components_ID - val_2(decimal)
    .....
    Components_ID - val_x(decimal)

    Magic potion2 with following column
    Components_ID - val_1(decimal)
    Components_ID - val_2(decimal)
    .....
    Components_ID - val_y(decimal) this one will have different number of components.

    and so on ...

    how should I store all my "Magic potion"-s record in a table? I will have probably 2000 records or maybe more.

    I'm thinking with database because of my project in ASP.NET with MS SQL 2008.

    thanks in advance.
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,163
    Rep Power
    4274
    Code:
    CREATE TABLE components 
    ( id INTEGER NOT NULL PRIMARY KEY 
    , name  VARCHAR(37)
    );
    CREATE TABLE potions 
    ( id INTEGER NOT NULL PRIMARY KEY 
    , name  VARCHAR(37)
    );
    CREATE TABLE potion_components
    ( potion_id INTEGER NOT NULL 
    , component_id INTEGER NOT NULL 
    , PRIMARY KEY ( potion_id, component_id )
    , quantity INTEGER
    );
    different potions will have different numbers of rows in the potion_components table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2013
    Posts
    2
    Rep Power
    0
    Thank you, for your help.

IMN logo majestic logo threadwatch logo seochat tools logo