PHP Developers Network

A community of PHP developers offering assistance, advice, discussion, and friendship.
 
Loading
It is currently Sun Feb 23, 2020 2:11 pm

All times are UTC - 5 hours




Post new topic Reply to topic  [ 22 posts ]  Go to page Previous  1, 2
Author Message
PostPosted: Wed Jan 25, 2012 2:44 pm 
Offline
Jack of Zircons
User avatar

Joined: Thu Nov 09, 2006 9:30 pm
Posts: 4484
Location: California, USA
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.


Top
 Profile  
 
PostPosted: Fri Jan 27, 2012 7:55 pm 
Offline
Forum Commoner

Joined: Wed Dec 14, 2011 1:17 pm
Posts: 36
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)).
File comment: Schedule
Schedule.JPG
Schedule.JPG [ 25.53 KiB | Viewed 4147 times ]


File comment: Games
Games.JPG
Games.JPG [ 16.29 KiB | Viewed 4147 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?


Top
 Profile  
 
PostPosted: Fri Jan 27, 2012 8:40 pm 
Offline
Jack of Zircons
User avatar

Joined: Thu Nov 09, 2006 9:30 pm
Posts: 4484
Location: California, USA
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.


Top
 Profile  
 
PostPosted: Wed Feb 22, 2012 1:30 pm 
Offline
Forum Commoner

Joined: Wed Dec 14, 2011 1:17 pm
Posts: 36
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?


Top
 Profile  
 
PostPosted: Wed Feb 22, 2012 5:00 pm 
Offline
Jack of Zircons
User avatar

Joined: Thu Nov 09, 2006 9:30 pm
Posts: 4484
Location: California, USA
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.


Top
 Profile  
 
PostPosted: Thu Feb 23, 2012 12:29 pm 
Offline
Forum Commoner

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


Top
 Profile  
 
PostPosted: Fri Feb 24, 2012 4:20 pm 
Offline
Jack of Zircons
User avatar

Joined: Thu Nov 09, 2006 9:30 pm
Posts: 4484
Location: California, USA
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.


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

All times are UTC - 5 hours


Who is online

Users browsing this forum: No registered users and 2 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