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!

[thetestingsite]

I just all depends on what it is you are using the above for. I like keeping my column names simple (and easy for me to remember), but also to where I still know what they are going to be used for. Very similar to what you have above is probably the way I would go about creating the database. The only thing that I see above that would be a problem (and I assume you editted these out for easier readability), VARCHAR would need to have the value/length value. And also the comp_id (unless you will be manually updating the id everytime you add info to the db), you may want to set it to auto_increment.

Example:

comp_id INT(5) auto_increment PRIMARY KEY
comp_name VARCHAR(200)

Other than that; as said above, it all depends on the use for the database that the structure should be based upon.

Hope this helps.

[mburt]

I use the TEXT keyword for long arguments as well.

[tomyknoker]

If anyone can give me an opinion as I've never made my own table before...

So basically the competition would have a Name, Description, Main Prizes, Consellation Prizes, Start Date and End Date. Also their might be 10 Main Prizes and 20 Consellation prizes, so there would be numerous codes, that could mean a competition entrant is a winner... Could someone have a look at the tables I've planned out, they don't seem 100% correct... I can't work out the linkage...

tblcompetitions
-------------------------
ID
comp_Name
Description
comp_Prizes
comp_Codes
StartDate
EndDate

tblcodes
-------------------------
ID
comp_Name
MainPrizeCodes
ConsellationPrizeCodes

tblprizes
-------------------------
ID
comp_Name
MainPrize
MainPrizeValue
ConsellationPrizes
ConsellationPrizesValue

[codeexploiter]

Hey tomyknoker can you explain what you are trying to achieve with these tables then can offer you more help anyway your latest table structures looks non optimized ones.

[tomyknoker]

Hey tomyknoker can you explain what you are trying to achieve with these tables then can offer you more help anyway your latest table structures looks non optimized ones.

Ok well basically I want to be able through a php page which I'll set up be shown competitions that I am running at any given time. From the start say I am beginning my first ever competition the key elements would be:

> Competition Name
> Competition Description
> Prizes For The Competition & Their Respective Prize Codes
> Competition Start & End Date

There would usually be maybe 5 major prizes (different for each comp) and then a whole lot of consellation prizes (also different for each comp). People would enter a prize code they received, and it would relate to one of the prize codes in the db... They could be a winner of a major prize, consellation prize or simply not a winner... So this is where I get confused about how many tables I should have and what links to make... Any help/opinions would be greatly appreciated!