View Full Version : My First Table

03-29-2007, 08:44 PM
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_name VARCHAR
comp_description VARCHAR
comp_prizes VARCHAR
comp_codes VARCHAR
comp_start DATE
comp_end DATE

Any opions/ideas would be much appreciated!

03-29-2007, 09:35 PM
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.

03-29-2007, 09:40 PM
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?

03-29-2007, 10:08 PM
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.

SELECT c.comp_name
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

03-29-2007, 11:39 PM
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?

03-29-2007, 11:43 PM
Yep... and you should make the primary key for the comp_prizes table a compound key of the two columns, comp_id + prize_code.

03-31-2007, 07:32 AM
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...