Displaying the results and inserting the same in a table

Not for 'how-to' coding questions but PHP theory instead, this forum is here for those of us who wish to learn about design aspects of programming with PHP.

Moderator: General Moderators

dharmeshb
Forum Commoner
Posts: 36
Joined: Wed Dec 14, 2011 12:17 pm

Displaying the results and inserting the same in a table

Post by dharmeshb »

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.
mikosiko
Forum Regular
Posts: 757
Joined: Wed Jan 13, 2010 7:22 pm

Re: Displaying the results and inserting the same in a table

Post by mikosiko »

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
dharmeshb
Forum Commoner
Posts: 36
Joined: Wed Dec 14, 2011 12:17 pm

Re: Displaying the results and inserting the same in a table

Post by dharmeshb »

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

Re: Displaying the results and inserting the same in a table

Post by califdon »

dharmeshb wrote: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.
This is a common mistake made by beginners; the number of tables is not an arbitrary factor you can "keep to a minimum"; in order for the database to work, it MUST be structured properly, as Mikosiko outlined for you. You may find it helpful to read a tutorial on the design of relational databases. I recommend:
http://www.dreamincode.net/forums/topic ... alization/ and
http://www.deeptraining.com/litwin/dbde ... esign.aspx
dharmeshb
Forum Commoner
Posts: 36
Joined: Wed Dec 14, 2011 12:17 pm

Re: Displaying the results and inserting the same in a table

Post by dharmeshb »

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

Re: Displaying the results and inserting the same in a table

Post by califdon »

dharmeshb wrote: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.)
Probably not. As you will learn if you read some tutorials on the design of relational databases, a database is a MODEL of some aspect of the real world. You, the designer, must form a clear picture of just what will be contained in your model. You start with identifying what ENTITIES will be modeled in your database, then how the entities are related to each other, then what ATTRIBUTES each entity needs to describe it. This is process that only the designer can successfully perform. If you follow these rules, the resulting database schema will allow you to use SQL queries to retrieve any information that is represented logically in the tables that this analysis leads to. Several years ago I wrote a tutorial for an Access forum, which applies to any relational database: http://forums.aspfree.com/microsoft-acc ... 08217.html. The full tutorial is attached to that post.
dharmeshb
Forum Commoner
Posts: 36
Joined: Wed Dec 14, 2011 12:17 pm

Re: Displaying the results and inserting the same in a table

Post by dharmeshb »

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

Re: Displaying the results and inserting the same in a table

Post by califdon »

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:
[text]+------------+ +------------+ +------------+
| Students | | Stud-Class | | Classes |
+------------+ +------------+ +------------+
| sID (PK) |<------| sID (FK) | +-->| cID (PK) |
| lName | | cID (FK) |---+ | classNo. |
| fName | | date | | className |
| phone | | grade | | instructor |
| ...etc. | +------------+ | ...etc. |
| |[/text]
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!
dharmeshb
Forum Commoner
Posts: 36
Joined: Wed Dec 14, 2011 12:17 pm

Re: Displaying the results and inserting the same in a table

Post by dharmeshb »

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

Re: Displaying the results and inserting the same in a table

Post by califdon »

It would take much greater familiarity with the way all those things work in real life than I have, or have time to acquire, in order for me to offer a valid analysis. This is the task of the database designer, to understand the way the real life entities relate to each other, then to define the entities, relationships, and attributes, which then leads to establishing the required tables and the attribute and key fields in each of those tables. As a general statement, I'd say that just writing down a bunch of fieldnames doesn't help. I recommend that you do several things:
  • Write down a list of your entities (not the table names, describe what they represent, using several words to explain what each one is).
  • Write down in plain language what all the relationships are ("Each game occurs in a specific season, each season many games are played, each game engages 2 teams, each team consists of many players, but only 11 players of a team engage in a specific game, ...").
  • Try to draw an Entity/Relationship diagram for your database. It doesn't have to be fancy, but it needs to show each entity in a box, with arrows connecting the entities that are related to each other. This might help: http://en.wikipedia.org/wiki/Entity-relationship_model.
  • For each entity, write down all the attributes you need to store for it (you won't need to store every possible attribute, but you will need to store all attributes that are necessary for identifying or performing calculations).
You will most likely have difficulty expressing some of these concepts, but that is exactly why it is important to do it! It is in the process of struggling to define these things that you will gradually clear up their real natures in your mind.

Only after you have reviewed the above and really satisfied yourself that you have correctly described your data model, THEN you can establish tables (each entity must have its own table, and each many-to-many relationship must have its own "middle" table), and you will know what fields need to be in each table (attributes and key fields, which will become apparent from the relationships that involve that table). That's the process. I have taught databases and done database consulting for over 15 years and I have never found a reliable substitute for this process.
dharmeshb
Forum Commoner
Posts: 36
Joined: Wed Dec 14, 2011 12:17 pm

Re: Displaying the results and inserting the same in a table

Post by dharmeshb »

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

Re: Displaying the results and inserting the same in a table

Post by califdon »

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.
dharmeshb
Forum Commoner
Posts: 36
Joined: Wed Dec 14, 2011 12:17 pm

Re: Displaying the results and inserting the same in a table

Post by dharmeshb »

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

Re: Displaying the results and inserting the same in a table

Post by califdon »

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:[text]tblGames:
GameID (PK)
TeamA_ID (FK)
TeamB_ID (FK)
DatePlayed
PlacePlayed
ScoreA
ScoreB
...
etc.[/text]
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.
dharmeshb
Forum Commoner
Posts: 36
Joined: Wed Dec 14, 2011 12:17 pm

Re: Displaying the results and inserting the same in a table

Post by dharmeshb »

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