Help me design my database...

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
big0mike
Forum Newbie
Posts: 5
Joined: Thu Jul 17, 2008 12:52 pm
Location: Peoria AZ

Help me design my database...

Post 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,
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: Help me design my database...

Post 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
Post Reply