Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 7 of 7

Thread: My First Table

  1. #1
    Regular Coder
    Join Date
    Mar 2006
    Posts
    459
    Thanks
    3
    Thanked 0 Times in 0 Posts

    My First Table

    Hi all,

    I'm doing my first MySQL table and wanted some opinions on the best and cleanest way to do it. It's a table which is going to have information about competitions. So I have named the following columns, is my naming ok? Would any of you have done it differently?

    comp_id INT PRIMARY KEY
    comp_name VARCHAR
    comp_description VARCHAR
    comp_prizes VARCHAR
    comp_codes VARCHAR
    comp_start DATE
    comp_end DATE

    Any opions/ideas would be much appreciated!

  • #2
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    The names are fine... I'm worried about the two columns comp_prizes and comp_codes, which by the names imply more than one, which you will want to separate into another table to keep your data normalized.

  • #3
    Regular Coder
    Join Date
    Mar 2006
    Posts
    459
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Ahhhh ok yes there will be maybe 50 codes, and probably 20 prizes, so would each of these need to be in a seperate table? How do I link back to the competitons table with these new tables? Or don't I need too?

  • #4
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    No no, not each prize in a separate table, but have a separate table that stores all the prizes (and one other table that stores all the codes).

    If you have the same prizes for many competitions, then you'll want a prizes table that looks like this:

    prizes table
    prize_code CHAR(8) PRIMARY KEY
    prize_desc VARCHAR
    prize_value DEC(9,2)

    That's it. The prize code will be unique, so it will be your primary key. (If you really want to, you can have a separate unique integer ID but it's not really necessary for this table.)

    To connect prizes with competitions, you need a table inbetween the two-- let's call it comp_prizes.

    comp_prizes table
    comp_id INT
    prize_code CHAR(8)

    That's it. You store a row in that table for every prize you want to connect to a competition. You then write queries that join the three tables together, i.e.
    Code:
    SELECT c.comp_name
    ,p.prize_desc
    FROM comp as c
    JOIN comp_prizes as cp
    ON c.comp_id = cp.comp_id
    JOIN prizes as p
    ON cp.prize_code = p.prize_code

  • #5
    Regular Coder
    Join Date
    Mar 2006
    Posts
    459
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Sorry to keep asking questions but will the comp_id be the primary key for the competitions table? Each table needs a primary key yes? Or should have one?

  • #6
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    Yep... and you should make the primary key for the comp_prizes table a compound key of the two columns, comp_id + prize_code.

  • #7
    Regular Coder
    Join Date
    Mar 2006
    Posts
    459
    Thanks
    3
    Thanked 0 Times in 0 Posts
    How does this look? I realised that each competition will have a whole lot of different prizes associated with it so but there will always be a grand prize, and then some littler prizes. So with 'tblcompetitions' 'Prizes' and 'Codes' confusses me. Also with the codes there might be 5 grand prizes and 80 conselation prizes. So the the 5 grand prizes will have a Uniqe code, as long as the 80 conselation prizes. So do I need to have a tblcodes? Anyway this is what I came up with for the first two tables...

    tblcompetitions
    -------------------------
    ID
    Name
    Description
    Prizes
    Codes
    StartDate
    EndDate

    tblprizes
    -------------------------
    ID
    Codes
    Description
    Value


  •  

    Posting Permissions

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