My First Table

[tomyknoker]

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!

[aj_nsc]

Field names look good and easy to understand. Personally, I'm a really lazy guy so if it were me, I would probably name the table `competitions` and omit the comp_ from the beginning of each of the fieldnames......but hey, whatever floats your boat.

The logic to setting up databases and tables is tricky (I'm not claiming to be an expert in it) but keep in mind the options other than VARCHAR. If your description field is going to be long, then maybe make it into a text field. Also, if you are going to use similar prizes for most of your competitions, then maybe you should make a separate table called prizes where you can store all your prizes and link it to each of your competition rows.....

Just things to keep in mind, they mightn't be applicable to you.

[tomyknoker]

I really like the idea of having a seperate table for prizes! So then in the competitions table would I need to add a column name prize_id or something?

[aj_nsc]

Let's say you have a prize for first, second, and third place in each competition. In the competitions table then you could have fields like firstPrize, secondPrize, and thirdPrize....and in each of these fields would be the corresponding prizeid of a prize that is stored in your prizes table.....know what I mean?

[tomyknoker]

Oh yea I see cool! Hmmm come to think of it the prizes would always change but there would be like a big prize and then a whole bunch of consolation prizes...

[tomyknoker]

Also so then what kind of field would the prizes column be in the Compeitions table? Or would it not have a prizes column if Prizes was going to have it's own table?

[aj_nsc]

you would only give prizes its own table if you would be using the same X number of prizes over and over again. it was just an example to illustrate db logic. check out a mysql introductory tutorial, something that gets you into the logic of databases and connecting tables, not so much the actual SQL. you'll get the idea.

[tomyknoker]

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