I am creating a league management system. I came up with the diagram below. I am no database expert but I feel it would be better to merge the three following tables: season_league, league_division and division schedule(see second image). This is for mySQL using doctrine with Symfony2 if it matters.

What are your opinions? Any suggestions?

enter image description here

enter image description here

  • If you merge them call it season_league_division Just saying. It's easier to query the second diagram than the first – sa555 Aug 30 '13 at 9:31
  • 1
    Sorry for my intense sports ignorance, but isn't a division a subset of a league? If so, I would assume that by definition, a division can't change leagues, so league_id should be an attribute of division, leaving the tables in question only needing to represent division+season. The 2nd model notably blurs the league/division relationship, incorrectly allowing a division to be mapped to different leagues, if my understanding is correct. – Michael - sqlbot Aug 30 '13 at 13:34

The decision of whether to merge these tables or not should depend on whether the separated tables provide useful information. Use the rules of database normalization. What are the functional dependencies? What are the candidate keys?

Season_League doesn't seem to have any information in it other than which leagues exist in which seasons (pure intersection) League_Division just adds division to this pure intersection. Division_Schedule doesn't seem to add any information at all.

One question I would have is whether it is useful to know that a league exists in a season. If you combine the tables, then what you have is a division exists in a league in a season. There is no "independent life" of a league except that it has a division. There are other subtleties too, like whether a division can exist in more than one league within a given season. If you combine the tables into one then this type of business rule could not be enforced with declarative referential integrity. Does this matter to you? It depends on what other manual or automated controls you're willing to put in place to manage this risk.

Without any more information about your intended business rules, I would say these three tables should definitely be combined.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.