Table structure in mySQL

1
votes

I'm in the early stages of creating a database using MySQL and PHP and would like some advice please. I have started to collate the data and would like to start typing it into .csv files ready to import into my tables. Before I do, I'm unsure how to layout my structured columns and tables properly.

Ok, I'll try my best to make clear what I'm trying to create. I'd have my home page structure where you have a choice of selecting a list of players by season or by an A-Z list of all-time players. Once you click on a specific player from the list of players it would show something like this for their player profile: http://stats.touch-line.com/playerdet.asp?playerid=41472&cust=2&lang=0&FromSTR=TRUE&compid=&teamid=1&H2H=

How many tables would I need to create?

A player table with playerID,playerName,playerDOB,playerBirthplace,playerPosition etc.

A team table with teamID,teamName,teamNickname,teamGround,teamFounded etc.

A season table with seasonID,playerID,teamID,playerApps,playerGoals?

Or is there a quicker, more efficient way without the need to use so many tables to link the data? Any advice would be much appreciated. Thanks in advance. ;)

1
phpmysqldatabasedatabase-design
You've got a good start, but consider that playerPosition might change through a player's career or even within a season. "center" on team #1, "goalie" on team #2, and maybe "benchwarmer" on team #3. – Marc B
@MarcB Thanks Marc. ;) playerPosition aside, I'm just trying to figure the most efficient way of combining the data. Ideally on the player profile it would have SEASON | TEAM NAME | APPS | GOALS but I'm unsure how I would store the data in a table. With so many entries would it run efficiently? Or would I need to create a table for each season? – julescoco
SQL databases run efficiently on tables containing millions of rows, so don't worry about 'so many entries'. What is important, though, is getting the data structure correct. – No'am Newman

1 Answers

1
votes

How many tables do I need to create?

The short answer is: one table for each "entity" type. An entity can be defined as a person, place, thing, concept or event, which can be uniquely identified, is of interest to the business, and we can store information about.

One key to database design is data analysis (Richard Perkinson "Data Analysis: The Key to Database Design", QED c.1993)

You've identified some of the important entities in your model: player, team, season. There may be some other key entities that are missing, which may be discovered later.

The attributes of each entity need to be identified, and should be dependent on the key of the entity, and not some other key. (Every attribute should be dependent on the key, the whole key, and nothing but the key, so help me Codd.)

You also need to identify the relationships that exist between the entities. Can a player be a member of more than one team? Can a player have more than one position? If a player is traded (moves from one team to another), how will that be represented in the model?

Where we encounter "many-to-many" relationships, those are represented in a separate relationship tables. Repeating attributes also get broken into separate child tables.

It's important that you get the model right, before you start combining multiple entities into the same table. Optimization usually results in a broken model; it usually doesn't fix a model that doesn't work.

Databases are designed to handle large number of rows efficiently, when the queries are in line with the model. Databases with dozens of tables can run very efficiently, and run more efficiently than databases with fewer tables.

I'd be more concerned with getting a database design that works, than I would be concerned with optimizing a design that doesn't work.