Need Help Creating Structure To Database

Im working on a website that processes statistics, for a sporting competition. I.E my site will let users quickly look up which player(s) scored the most points in a game/season or

Solution 1:

Capture the statistics at the lowest applicable level. You want to apply the principles of database normalization to your initial design. This gives you the most straight-forward data capture scenario while leaving you the most flexibility for reporting.

Once you decide exactly what reporting you need and have determined what performance challenges you may be facing, then you can apply denormalization to create reporting data warehouse tables.

Consider the following logical data model:

Solution 2:

If the hope of teaching how to fish....

Write out what Entities you think you have, and what attributes they own. An attribute might be another Entity. For example a Team entity has a Name attribute, and multiple Player attributes. But Player is also an Entity with a Name attribute and maybe some Statistics attributes.

This will give you your starting point for Tables (Entities) and their columns (Attributes). Where an Attribute is also an Entity, draw a line from the Attribute to the Entity: those are your relationships. Then get hold of an example of putting data into Third Normal Form (3NF) and follow tghe example applying the steps to your diagram. When you've done that, you'll have a good DB Design.

Cheers -

Solution 3:

You should join all your "Game" tables into one table. Just add the needed extra info:

Columns:

• gameId - int - game id, former part of table name
• scoreA - first teams score
• scoreB - second teams score
• teamAId - Id of first team, former name of column
• teamBId - Id of second team, former name of column

This would also enable you to do much more interesting searches and statistics on all of your games at once. And better yet, you have a flexible structure than can be used for any number of games and teams that may come in the future.