Schema Normalization :: Composite Game Schedule Constrained by Team

Related to the original generalized version of the problem:http://stackoverflow.com/questions/6068635/database-design-normalization-in-2-participant-event-join-table-or-2-column

As you'll see in the above thread, a game (event) is defined as exactly 2 teams (participants) playing each other on a given date (no teams play each other more than once in a day).

In our case we decided to go with a single composite schedule table with gameID PK, 2 columns for the teams (call them team1 & team2) and game date, time & location columns. Additionally, since two teams + date must be unique, we define a unique key on these combined fields. Separately we have a teams table with teamID PK related to schedule table columns team1 & team2 via FK.

This model works fine for us, but what I did not post in above thread is the relationship between scheduled games and results, as well as handling each team's "version" of the scheduled game (i.e. any notes team1 or team2 want to include, like, "this is a scrimmage against a non-divisional opponent and will not count in the league standings").

Our current table model is:
Teams > Composite Schedule > Results > Stats (tables for scoring & defense)
Teams > Players
Teams > Team Schedule*
*hack to handle notes issue and allow for TBD/TBA games where opponent, date, and/or location may not be known at time of schedule submission.

I can't help but think we can consolidate this model. For example, is there really a need for a separate results table? Couldn't the composite schedule be BOTH the schedule and the game result? This is where a join table could come into play.

Join table would effectively be a gameID generator consisting of:
gameID (PK)
gameDate
gameTime
location

Then revised composite schedule/results would be:
id (PK)
teamID (FK to teams table)
gameID (FK to join table)
gameType (scrimmage, tournament, playoff)
score (i.e. number of goals)
penalties
powerplays
outcome (win-loss-tie)
notes (team's version of the game)

Thoughts appreciated, has been tricky trying to drilldown to the central issue (thus original question above)

-------------Problems Reply------------

I don't see any reason to have separate tables for the schedule and results. However, I would move "gameType" to the Games table, otherwise you're storing the same value twice. I'd also consider adding the teamIDs to the Games table. This will serve two purposes: it will allow you to easily distinguish between home and away teams and it will make writing a query that returns both teams' data on the same row significantly easier.

Games
gameID (PK)
gameDate
gameTime
homeTeamID
awayTeamID
location
gameType (scrimmage, tournament, playoff)

Sides
id (PK)
TeamID (FK to teams table)
gameID (FK to games table)
score
penalties
powerplays
notes

As shown, I would also leave out the "Outcome" field. That can be effectively and efficiently derived from the "Score" columns.

Category:schema Views:0 Time:2011-05-20

Related post

  • Insert rows from one table to another, with identical schemas and composite keys on SQL Server 2011-03-03

    I have two tables with identical schemas and composite keys, table a and table b I need to insert rows into table b from table a, where an identical key does not already exists in table b. How do I do this? --------------Solutions------------- -- Set

  • Database schema normalization checker? 2009-02-12

    I'm interested in learning about tools that operate like this: Given a database schema and some data, suggest whether the schema is likely structured in any particular normal form, and then tell how the schema might be factored to produce further nor

  • Hibernate with legacy DB schema and composite FKs and PKs 2012-04-11

    I am having some trouble trying to find a solution for this and I think it may not be possible at all, but I'll still give it a try here. So lets pretend my legacy DB schema on MySQL with MyISAM (no FKs) has the following 2 tables: TblOrder ORDERID (

  • How does one create a schema for composite types? 2010-08-21

    I have an XML spec that looks like this: <Root> <Directory Name="SomeName"> <TextFile Name="ExampleDocument.txt>This is the content of my sample text file.</TextFile> <Directory Name="SubDirectory"> <Speech Name="Exam

  • Scheduling, rosters and Team Calendars in Outlook 2010 - Desperately need help please 2012-01-12

    Wondering if someone can help. My department has just converted to outlook 2010. I am wanting to create a team roster that can be viewed by my staff each day so they are able to know what there duties are for the day. Is this possible? Also I would l

  • sharing a view from a project schedule to other teams 2013-01-19

    I've created a view in a project schedule that I need to share out to others. The overall columns will be the same across the board but the data will vary. I attempted to add that view to my global template, but when i try to pull it into another fil

  • In terms of databases, is "Normalize for correctness, denormalize for performance" a right mantra? 2008-11-16

    Normalization leads to many essential and desirable characteristics, including aesthetic pleasure. Besides it is also theoretically "correct". In this context, denormalization is applied as a compromise, a correction to achieve performance. Is there

  • How to automatically generate a sports league schedule 2009-06-24

    I'll start of by saying that I understand that this topic is complicated and that there probably isn't an easy answer. If it were easy then everybody would be doing it. That being said... I've been asked to build an application to manage a sports lea

  • Fluent NHibernate and composite ID with single column name 2009-10-17

    I've crossed posted this in the FNH Google Group but here's the scoop: I have a table in a legacy database that has a composite ID. Well, the PK is implied since the table doesn't actually have a PK defined, but the natural PK is the combination of t

  • Are there any tools for schema migration for NoSQL databases? 2009-12-25

    I'm looking a way to automate schema migration for such databases like MongoDB or CouchDB. Preferably, this instument should be written in python, but any other language is ok. --------------Solutions------------- Since a nosql database can contain h

  • hibernate creates problem for table with composite primary key while generating hibernate classes with eclipse 2009-12-29

    I have 2 tables as given below. feedback_answer question_id number(primary key) answer_id number (primary key) answer_en varchar2(255) answer_it varchar2(255) user_feedback verification_id number(primary key) question_id number(foreign key - feedback

  • Schema design: many to many plus additional one to many 2010-05-05

    I have this scenario and I'm not sure exactly how it should be modeled in the database. The objects I'm trying to model are: teams, players, the team-player membership, and a list of fees due for each player on a given team. So, the fees depend on bo

  • Is the version number in rails schema.rb used for anything 2010-06-05

    Now that Rails has timestamped migrations, the single version number at the top of /db/schema.rb seems pointless. Sometimes the version number ends up incorrect when dealing with multiple developers, multiple branches, ... Does rails even utilize tha

  • UTC or local time with zone offset for my schema? 2011-01-31

    I am defining a schema for my web service that will be accessed from multiple countries. I am wondering which of the below 2 should be used (both are valid according to xsd dateTime type and ISO 8601) and which one of them is WS-I compliant? UTC form

  • How to construct an NHibernate query based on the following schema? 2011-05-17

    I'm trying to construct an NHibernate query to get a list of countries that a Share Class is available for sale (AFS) in. This is the definition of the AFS table: CREATE TABLE [MStar].[AFS]( [AFS_ShareClassId] [int] NOT NULL, [AFS_CountryId] [int] NO

  • Problem with one-to-many mapping using composite keys 2011-07-07

    I have two tables in a legacy database that I need to map using NHibernate. Unfortunately, one of these tables are using a composite key and I am running into a problem which I describe below. Firstly, here is the schema of the two tables: CREATE TAB

  • Composite key in XML hibernate with second table 2011-07-25

    Firstly, I have no control over the technology or database structure. I have an object that has another object as it's composite key. Inside that composite id object, I have a column which I need to use to get ANOTHER value, specifically a descriptio

  • What's the difference between a catalog and a schema in a relational database? 2011-08-11

    I used to think schema were the "upper wrapper" object before the database itself. I mean DB.schema.<what_ever_object_name_under_schema>. Well, the catalog "wrapper" is now quite confusing. Why should we need a catalog? For what purpose, precis

  • Map a composite key inside a composite key hibernate xml 2011-08-15

    This is what I would like to do, map an object to another table that has the same primary keys. The below is an example, basically I have one object with a composite key that has a composite key for ANOTHER table, but I don't know how to include both

Copyright (C) dskims.com, All Rights Reserved.

processed in 0.088 (s). 11 q(s)