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

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'm afraid that I have little interest in sports, so I don't think an explanation would be useful to me. I wouldn't want to take the time to understand something that I'll surely never encounter, myself. Where I may be able to help you is not in judging exactly how your schema should look, but rather in trying to explain the principles behind it so that you can determine that yourself. This is somewhat like the principle, "Give a man a fish and feed him for a day, or teach him how to fish and feed him for life." I hope you understand.
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 »

Sorry for late reply. Here is the schema of two of the many tables. The Enity I am struggling with is Games. I have attached the schema as screenshot. The GameID in the Games table is coming from the table Schedules and TeamID is coming from a reference table called TeamsForSeasons (id, Seasonid (FK), Teamid (FK)).
Schedule
Schedule
Schedule.JPG (25.53 KiB) Viewed 6569 times
Games
Games
Games.JPG (16.29 KiB) Viewed 6569 times
My goal is to store the statistics of each game in a table but a game has two teams, so how do I handle that?
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 »

Take a closer look at what you have there: you have a table named Games, but the primary key is not the GameID! Why? The purpose of a primary key is to uniquely identify each instance of the entity that table represents. The primary key of the Games table is, almost by definition, GameID. Instead, you have another table named Schedule, with a primary key of GameID, which appears to contain all the attributes of a game. That's where your confusion arises. You have 2 tables you are trying to use to represent the same thing. In the real world, what is a schedule? I'm pretty sure it is nothing more than a collection of games and shouldn't require any table at all, because it isn't really a definable entity. When you want to display a schedule, you just write a query to select and order the rows from the Games table. This is exactly why it is important to begin with careful written definitions of your entities, relationships, and attributes. This will help you review and understand the real world situation that you will build a data model to represent.

One of my best friends, whom I have known for over 30 years and worked with on several projects, argues with me over this point. He claims that his mind just doesn't work like that, and he always just starts creating tables and refining the schema as he gets further and further along. It would be hard for me to say this to him (I'm reasonably sure he will never see this), but I don't consider him a good database developer at all. He is inefficient and he has made a real mess of a couple of database designs that I am aware of. He is stubborn (well, we both are) and insists that he doesn't want to try to do this sort of analysis up-front. He is very intelligent (holds a PhD in Chemistry!) and I am convinced that if he would just approach his database projects in a more organized manner, he would be able to create more solid database schemas and do so considerably faster than his trial-and-error method.

So I would suggest that you combine those 2 tables into a single Games table, eliminating the ScheduleID, which doesn't represent anything that I can think of. Once you have entered dates for each game of a season, your schedule is simply the selection of games that have dates within that season. At most, you might decide to have a tiny little table that defines the beginning and ending dates of each season, which you could use to select the games that are considered to be played in each season.
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 »

My PHP development was on a pause for few weeks. I understand your suggestions califdon. For all the other tables I have the structure that you suggest but for the way I need to store data for each game it gets confusing unless I have a model like this

GameID
Date
Team1
Team2
Team1Runs
Team1Wickets
Team1Extras
Team2Runs
Team2Wickets
Team2Extras
Winner

Is this a good model?
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 might be. Only someone thoroughly familiar with the subject matter could answer that.

I assume that you are describing your Games table. Therefore, in addition to the primary key and any foreign keys, if there are any, all the fields should be attributes of a Game and their values entirely dependent on the GameID. That is, they don't depend on anything else. So you can ask yourself questions like "Is the value of Team1Runs an attribute of (does it entirely depend on) the value of GameID?" To me, it looks like it does. This is the same question you must ask about every field. I would assume that Team1 and Team2 are foreign keys, linking the entity Teams to the entity Games. When listing fields like this, you should always indicate the primary and any foreign keys, in order to make it clear. What is unclear, though, is what you may have left out! I have no way of knowing what you need for your model, this is why the database developer absolutely MUST understand the real-life system being modeled. Would the Place the game was played be needed? Would the name(s) of the Referee(s) be important? etc. etc. This is the whole job of the database developer, to understand the real-life system and construct a database model of it. Nobody else is likely to be able to do it for you.
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 »

Thinking about it again.. schedule is a real life entity.. Schedule will be created before a league starts.. changes can happen before it is finalized.. Games table will have data after a game is completed. So my two tables would look like

Schedule
ID
HomeTeamID [FK] (from Teams table using the reference table TeamsForSeasons)
AwayTeamID [FK] (same as above)
UmpiringTeamID [FK] (same as above)
Date
StartTime
LocationID [FK]
Round

Games
ID
ScheduleID [FK] (from Schedule table above)
TossWinner
GameWinner
HomeTeamRuns
HomeTeamWickets
HomeTeamOvers
HomeTeamExtras
AwayTeamRuns
AwayTeamWickets
AwayTeamOvers
AwayTeamExtras

I feel it will be easier to manage by having them in separate tables.. Unfortunately I am the BA, Database modeler, Developer, and Tester.. so I will need to take the best decision with the available information. Califdon, your insights have helped me to answer lot of questions and clarify things. Thanks for that.
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 »

You could be correct about Schedule being a real-life entity, although it doesn't sound to me like it is. Just the timing of when the information is available doesn't impress me as a factor in whether it is an entity or not. What matters is whether you can describe it as an independent "thing" that doesn't depend on anything else, and has attributes that are unique to it and not to other entities. But I admit that this is getting fairly academic. However, it is the academic viewpoint that largely determines whether a database design will efficiently handle the storage and retrieval of data. Whenever I see a statement like "I feel it will be easier to manage by having them in separate tables," I cringe! That is not a useful rationale unless you can be much more specific as to WHY you feel that way. You might as well say "I feel that 8 plus 5 equals 12." "Feelings" have no place in database design. There are quite specific rules that govern relational databases, and if you ignore them, be assured that you will have trouble further along in your design.

So I still think that a "schedule" consists of a subset of "games". A schedule may be incomplete, a schedule may be subject to changes, but the data is nothing more than what should already be in the "games" table, in my opinion. Of course, as I have said all along, I don't have a good understanding of the problem space you are working with, so I might be wrong. My main intent here is to expose you to the kind of analysis that needs to go into the design of any but the simplest of relational databases. You will make your own decisions and your own mistakes, just as every other developer does. Certainly including me.
Post Reply