I need some addition thoughts on how to approach a database design I'm working on. The issue is I have three related items...
* Clan (has many games) * Game (belongs to a clan, has many teams) * Team (belongs to a game) Clan -< Game -< Team
Each of them is also treated as a "Site" and they can only ever have one site. Each site has a standard set of properties, but Clan / Game / Team each have their own unique properties. It's like Clan, Game & Team are subtypes of a Site. How would I best model this in a database as the relationships go?
Idea 1: A 1-to-0/1 relationship between multiple tables (doesn't feel like it's a good idea).
Sites object_id (primary key, uuid) + additional site properties Clan object_id (primary key, foreign key sites.object_id) + additional team properties Game object_id (primary key, foreign key sites.object_id) + additional team properties Team object_id (primary key, foreign key sites.object_id) + additional team properties
Idea 2: Clan, Game & Team each have a column "site_id" that is a foreign key to "site.id". This makes sense but doesn't provide a way in the database to enforce one Clan/Game/Team per site.
Idea 3: A relationship table, (id, item_type [clan,game,team], item_id, site_id), with a unique constraint on the site_id. I'm sure this is a "no-no" as I can't do any foreign keys on item_id.
Idea 4: Multiple relationship tables (clan_site, team_site, game_site), still doesn't solve limiting a site to only one item.
Idea 5: Have clan_id, game_id and team_id columns added to the site table. Although this would work for foreign keys, it doesn't seem right as only one of those columns would hold a value. Can't think of a way to stop two or more columns being populated and since NULL is supposed to be unknown, would that mean I should really have: clan_id = 2, game_id = 0, team_id = 0 ?
I'm probably missing something obvious or looking at this from the wrong side. :)