Page 1 of 1
Help me design my database...
Posted: Mon Jul 21, 2008 11:06 am
by big0mike
I'm in the process of learning PHP and MySQL and I plan on developing a database application for my poker/cornhole games for registration and tracking players and their results. But, since this is the first database I will be constructing I'm not clear on the design/logic I'll need and how to interconnect the different tables.
I'll need tables for PLAYERS (to hold player info obviously) and TOURNEYS (to hold tourney dates) and I think I'll need another two tables for FINISHES and STANDINGS.
Possibly more but that's the basics of what I'm thinking. The problem is I don't wanna build more tables than is necessary. But I also don't wanna make a table that should be simple (PLAYERS) too complex by adding stats, finishes and scores. I would think a PLAYERS field would be the field I need in all tables in order to cross-reference each other properly.
Does it sound like I'm on the right track? Suggestions?
Thanks,
Re: Help me design my database...
Posted: Mon Jul 21, 2008 3:25 pm
by califdon
big0mike wrote:I'm in the process of learning PHP and MySQL and I plan on developing a database application for my poker/cornhole games for registration and tracking players and their results. But, since this is the first database I will be constructing I'm not clear on the design/logic I'll need and how to interconnect the different tables.
I'll need tables for PLAYERS (to hold player info obviously) and TOURNEYS (to hold tourney dates) and I think I'll need another two tables for FINISHES and STANDINGS.
Possibly more but that's the basics of what I'm thinking. The problem is I don't wanna build more tables than is necessary. But I also don't wanna make a table that should be simple (PLAYERS) too complex by adding stats, finishes and scores. I would think a PLAYERS field would be the field I need in all tables in order to cross-reference each other properly.
Does it sound like I'm on the right track? Suggestions?
Thanks,
The correct way to make these decisions is to clearly define the
entities to be represented in your database. Each entity will become a table. There should be no guessing or wondering how many tables you need. The first and obvious entity is Players, as you have already recognized. Similarly, a Tourney (an event) is an entity. The decision is whether Finishes and Standings are entities apart from Players and Tourneys, or whether they are attributes of one or the other. What is meant by Finishes and Standings? Are Finishes just the rankings of scores in Tourneys? Then what are Standings, over-all scores? I don't know much about gaming, but if these are just based on scores, I would think the entity is Scores. There would be a many-to-many relationship between Players and Tourneys, with the Scores table being the linking table, like this:
Code: Select all
[u]tblPlayers[/u]:
PID
Pname
[u]tblTourneys[/u]:
TID
Tplace
Tdate
[u]tblScores[/u]:
PID
TID
Score
With that, you could easily display the ranking of Players for any Tourney, or the overall ranking of Players over a time period, etc.
You might find it helpful to read some tutorials on relational database schemas, such as:
http://www.campus.ncl.ac.uk/databases/d ... esign.html
http://www.library.cornell.edu/elicense ... _final.doc