File structure questions


id    int(10) auto_increment primary key
team_id  int(10)
year    varchar(4)
team   ???
mascot  ???
sport   int(11)
state   int(11)
conference    ???
classification  ???

I have these questions:

#1 - Should team be an integer linking it to a team table or just put the team name within it.

#2 - The same question as number one, but pertains to mascot table.

#3 - Conference would most likely be something similiar “East”, “Central”, etc. Would it just be best to put that in it, or make a table and put the descriptions in, linking them with a foreign key

#4 - Same as the above, but would hold information akin to “AAAAA” or “AA”, etc…

Which would be faster and more friendly when trying to extract data/compare things?

The thing is that each team would have to have conference and classification put together too, like: AAAA East, AA Southwest Things of that nature

id, team_id, year, school, mascot, sport, state, conference, classification

1,1,2011,1,1,1,4,1,1
or
1,1,2011,Oak Ridge,Timbers,Football,KY,West,AAA

Sorry more questions than answers

You have a team_id field so presumably you already have a team table with the name of the team in it. So why would you replicate the name in this table?

What information is this table actually holding - not the fields (you have given them) but it’s purpose?

Am I right in thinking that a lot of the other fields pertain to the team and would more appropriately be part of that table?

One more thing why does year have a data type of varchar(4) and not integer?

As a general answer if one or more fields have a fixed set of values and you want to maintain an ensure that consistency then it is usually better to put the possible values in another table and reference them.