PHP Developers Network

A community of PHP developers offering assistance, advice, discussion, and friendship.
 
Loading
It is currently Tue Feb 25, 2020 1:18 am

All times are UTC - 5 hours




Post new topic Reply to topic  [ 22 posts ]  Go to page 1, 2  Next
Author Message
PostPosted: Fri Dec 30, 2011 9:58 am 
Offline
Forum Commoner

Joined: Wed Dec 14, 2011 1:17 pm
Posts: 36
Hello,
A little background.. I recently started to think about web development a bit seriously and as my first personal project I am building an app with PHP & MySql, which will be used as a cricket league manager.
Some of the tables that this question is concerned with are
Seasons (SeasonID, LeagueID, SeasonYear)
Leagues (LeagueID, LeagueName)
Teams (TeamID, TeamName)
TeamsBySeason (TeamsBySeasonID, SeasonID, LeagueID, TeamID)
Players (PlayerID, PlayerName, LeagueID, SeasonID, TeamID)
Games (GameID, HomeTeamID, AwayTeamID, TotalScore, TotalWickets, WonBy, GameDate, GameLocation)

I want to have a page which will show my a list of all teams from 2010 Season. From that list I want to choose which teams will play this season and add them to teamsbyseason table with updated SeasonID.

Similar thing will happen with Players. I want to see a list of players from last season and then add them to players tables with Updated SeasonID and if players change teams for next season than the TeamID and SeasonID will be updated.

Any suggestions on the database model will be helpful as well. I kind of struggle when I am trying to view or insert data using the relations I have setup.

Thanks.


Top
 Profile  
 
PostPosted: Fri Dec 30, 2011 2:43 pm 
Offline
Forum Regular

Joined: Wed Jan 13, 2010 8:22 pm
Posts: 757
IMHO your Data Model could be improved:
- TeamsBySeason should have only the column (TeamsBySeasonID, SeasonID, TeamID) .... LeagueID is already in the table SEASONS, therefore here is redundant
- Players Should be only a base table with columns (PlayerID, PlayerName)

- You need a "bridge" table between TEAMS and PLAYERS
TeamsPlayers ( TeamID, PlayerID) .... the attributes LEAGUEID & SEASONID that you have in your table PLAYERS currently are implicit in the relationships, however could belong to this table in case that you want to maintain the history by LEAGUE and SEASONS.

those changes should simplify your operations and make your model more flexible.
- You will assign PLAYERS to TEAMS (TeamsPlayers table)... no matter in which SEASON OR LEAGUE that TEAM(s) participate.
- You assign TEAMS to SEASONS (TeamsSeason Table)... is no SEASONID UPDATE here.. only NEW records (maintaining the history)

hope this help a bit


Top
 Profile  
 
PostPosted: Fri Dec 30, 2011 8:30 pm 
Offline
Forum Commoner

Joined: Wed Dec 14, 2011 1:17 pm
Posts: 36
Thanks Mikosiko. Your tips are helpful. I will make the suggested changes. I was trying to keep the number of tables to minimum.

I am still looking for the suggestion on how to display the teams and players, then add them for current season.


Top
 Profile  
 
PostPosted: Sat Dec 31, 2011 12:13 am 
Offline
Jack of Zircons
User avatar

Joined: Thu Nov 09, 2006 9:30 pm
Posts: 4484
Location: California, USA


Top
 Profile  
 
PostPosted: Sat Dec 31, 2011 2:58 pm 
Offline
Forum Commoner

Joined: Wed Dec 14, 2011 1:17 pm
Posts: 36
Thanks califdon. I will look into your links.. It gets a bit confusing when storing player statistics.. is this the right way to create a stats table?
Stats (StatsID, GameID, LeagueID, SeasonID, PlayerID, Runs, Balls, OutOrNotOut... etc.)


Top
 Profile  
 
PostPosted: Sat Dec 31, 2011 3:38 pm 
Offline
Jack of Zircons
User avatar

Joined: Thu Nov 09, 2006 9:30 pm
Posts: 4484
Location: California, USA


Top
 Profile  
 
PostPosted: Sun Jan 01, 2012 5:07 pm 
Offline
Forum Commoner

Joined: Wed Dec 14, 2011 1:17 pm
Posts: 36
The tutorial was helpful. The part where you said "how the entities will relate to each other" is the part that is getting me. I can decide in most cases how they are related but in some, for eg. in my case it is the statistics table which will have the statistics of a player for a particular game, is hard to identify the relationship.

Are there any steps or questions that will help me in making a decision? Also, is there any rule of thumb for the number of bridge/reference tables that can be created?

Thanks for the help.


Top
 Profile  
 
PostPosted: Sun Jan 01, 2012 6:18 pm 
Offline
Jack of Zircons
User avatar

Joined: Thu Nov 09, 2006 9:30 pm
Posts: 4484
Location: California, USA
Yes, there is a firm rule that says "You can and should create as many tables as are required by your schema; no more and no less." I apologize for being facetious, but that is literally true. Get it out of your mind that there is any significance to how many tables your database contains. You need whatever you need to describe your data model.

Now, as to the "how the entities will relate to each other" issue: You are correct, that's the difficult part and that is why it is so important to approach it in a disciplined manner. It requires you to give a lot of thought to your data, and it is only when you do that, that you will create a data structure that will properly meet your needs. It will save you from putting a lot of time and effort into setting up a database that turns out to be unusable, requiring you to start over again, perhaps several times! Typically the way to express relationships is something like, "one sales order may have many line items" (that's a one-to-many relationship), or "one student may enroll in many classes, AND one class may have many students" (that's a many-to-many relationship). But some schemas are more complicated and may require a lot more thinking. And writing down. Writing your schema description on paper helps you to get the details clearly in your mind. Don't be afraid to tear up your descriptions several times and start over; it's much easier than creating schemas on the computer that will ultimately fail to meet your needs. I don't know enough about your application or the general field of games and statistics to give you an answer, but if I were doing it with the limited knowledge I have of such systems, I think I would ask myself questions such as, "What entities have I identified that would be involved in generating statistics? How do they relate to each other?" Probably you have identified entities more or less like, Players, Teams, Games, maybe Leagues? What attributes of those entities would be used in generating statistics? Maybe the points won in the games? Are they team points or individual points? Are those points stored as attributes in the Games table? I would offer a guess that there is a relationship between Players and Games, such that one player may participate in many games AND one game may have many players. Those are the kind of relationships you must identify.

A one-to-many relationship requires that there be corresponding primary and foreign keys in the 2 tables, enabling the one-to-many relationship between them. But a many-to-many relationship requires there to be a 3rd table, sometimes called a "joining" table, that only requires the foreign keys to the other 2 tables, although it may also have other data fields (such as a transaction date or something).

Another way to think of these intermediate, or "joining" tables is that they represent "an instance" or transaction between the other 2 entities. As an example, the classic students-and-classes case looks something like this:
Syntax: [ Download ] [ Hide ]
+------------+       +------------+       +------------+
| Students   |       | Stud-Class |       | Classes    |
+------------+       +------------+       +------------+
| sID (PK)   |<------| sID (FK)   |   +-->| cID (PK)   |
| lName      |       | cID (FK)   |---+   | classNo.   |
| fName      |       | date       |       | className  |
| phone      |       | grade      |       | instructor |
|   ...etc.  |       +------------+       |   ...etc.  |
|                                         |

The "middle" table is often given a name that suggests which 2 tables it is joining, as shown above. In order to form the join, only those 2 foreign key fields (FK) are required, but since each row in the Stud-Class table represents an instance of a student who has enrolled in a class, that's the logical place to store the date she completed the class and the grade that was assigned. Doesn't that make sense? So you can think of the "middle" table either as a special table that joins 2 other tables (which it does) or as a subordinate or dependent entity that represents enrollments.

This is the kind of thought process that is at the heart of designing a relational database.

Good luck with your project and come back to us with questions as you go along.

And Happy New Year!


Top
 Profile  
 
PostPosted: Mon Jan 02, 2012 2:29 pm 
Offline
Forum Commoner

Joined: Wed Dec 14, 2011 1:17 pm
Posts: 36
Thanks califdon. The student-class example, after re-reading few times helped to understand the basics..

I am developing a cricket league manager (similar to baseball) where a league will have multiple seasons and one season will have multiple teams.. a player can only belong to one team in a season but they can switch teams upto certain point in the season.. each team has 11 players for a game but each team can have a roster of up to 16-20 players..

I plan to use the game table to store schedule as well as the stats for the game at team level.. eg. total score of the team, total wickets lost.. (GameID, SeasonID, HomeTeamID, AwayTeamID, UmpiringTeamID, GameDate, GameLocation, WinningTeamID, TotalScore, WicketsLost).. while writing this I realized I need a table for Location to store City name and ground name :) am learning ;)

BattingStats table will have data for each player in a game (StatsID, PlayerID, GameID, Runs, HowOut, DismissalMode, Fours, Sixes, BowlerID).. BowlerID is to know which bowler got the batsman out...

Now I am unsure if I want to display a table showing the stats for a batsman for current season will this work? Am I missing a relation?

I am appreciating all the help I am getting here.. sometimes tutorials are just not enough to get it so asking on forums and learning from others really helps to get that extra push..


Top
 Profile  
 
PostPosted: Mon Jan 02, 2012 11:47 pm 
Offline
Jack of Zircons
User avatar

Joined: Thu Nov 09, 2006 9:30 pm
Posts: 4484
Location: California, USA


Top
 Profile  
 
PostPosted: Tue Jan 03, 2012 11:31 am 
Offline
Forum Commoner

Joined: Wed Dec 14, 2011 1:17 pm
Posts: 36
I cannot less than agree with you. I "kinda" followed the process, except that I did not realize to put the design in plain English. I have the ER diagram and when I started developing thinking that I have everything figured out I started running into few issues. I will go back to the basics and get things on paper. Hopefully that will help me to refine my database.

There is no shortcut to this as far as I know :S.. well there is no shortcut to hard work :)


Top
 Profile  
 
PostPosted: Tue Jan 03, 2012 7:20 pm 
Offline
Jack of Zircons
User avatar

Joined: Thu Nov 09, 2006 9:30 pm
Posts: 4484
Location: California, USA
Yes, that's the lesson I try to get through to beginners. Sure, it's easy enough to create a simple database, even one with a relationship or two, but when it comes to designing something more complicated, you absolutely must follow a strict method that assists you to collect and organize your data so that you can process SQL query logic against it to produce desired results. And yes, it is sometimes hard work, and there are no shortcuts. I think you are on the right path.


Top
 Profile  
 
PostPosted: Mon Jan 23, 2012 2:04 pm 
Offline
Forum Commoner

Joined: Wed Dec 14, 2011 1:17 pm
Posts: 36
Thanks califdon.

I am very skeptical if I have the correct design for one of the tables. I want to store statistics for every game but at team level. Eg. Game 1 = Team 3 v Team 5. I plan to store data in one table "GameStats" as follows

id GameId TeamId TossWonBy Innings Runs Wickets Overs
1 1 3 3 2 150 5 35
2 1 5 3 1 180 10 32

In plain English it would read like this
In Game 1,
Team 3 won the toss and elected to field in the 1st innings. Team 5 scored 180 runs in 32 overs and lost all 10 wickets
Team 3 batted in 2nd innings, scored 150 runs in 35 overs and lost 5 wickets.

Result is Team 5 won by 30 runs. This result will be populated on the schedule page after a game is played.

Do you see any potential for confusion in the way I have designed the table?

Please advise.


Top
 Profile  
 
PostPosted: Mon Jan 23, 2012 2:36 pm 
Offline
Jack of Zircons
User avatar

Joined: Thu Nov 09, 2006 9:30 pm
Posts: 4484
Location: California, USA
I have some confusion, but it may be due to my unfamiliarity with the sport of cricket. :) Whenever I look at a database table, I try to understand what real life entity it represents. In your example, I don't understand what that is. "GameStats" doesn't seem to me to be a real life entity. Why would these values not be attributes of the "Game" entity? May I suggest you show us the schema for the Games table. I prefer a display rather like this:
Syntax: [ Download ] [ Hide ]
tblGames:
   GameID  (PK)
   TeamA_ID  (FK)
   TeamB_ID  (FK)
   DatePlayed
   PlacePlayed
   ScoreA
   ScoreB
   ...
   etc.

The question to ask yourself is: Are the "stats" descriptive of the "game"? That is, is there a one-to-one relationship between "stats" and a "game"? If so, they probably should be attributes stored in the Games table. With my limited knowledge of this real-life situation, it sounds to me like they should. It all comes down to the fact that you are trying to create a computer model of something in the real world. Absolutely everything in your model should represent an identifiable aspect of the real world situation.


Top
 Profile  
 
PostPosted: Wed Jan 25, 2012 11:55 am 
Offline
Forum Commoner

Joined: Wed Dec 14, 2011 1:17 pm
Posts: 36
Thanks califdon. I will post the schema tonight and try to be clear and give you a brief idea of the sport. Do you understand baseball? I may try to relate to Cricket to baseball. I understand that it will be hard to make any suggestions without understanding the real life situation.


Top
 Profile  
 
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 22 posts ]  Go to page 1, 2  Next

All times are UTC - 5 hours


Who is online

Users browsing this forum: No registered users and 3 guests


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum

Jump to:  
Powered by phpBB® Forum Software © phpBB Group