One to one relationship or another approach?

Question

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. :)

Answer 1

To remove extra fields the best approach (In my eye of course) would be to insert site_id to clan,team and game tables.

In the site table we have site_id which is primary key(unique) and we have a field in the name of url.

• url field should be unique.

• one-to-one relationship between your three tables with site table.

This is the way to go.

Answer 2

Your "Idea 1" is the best approach as it most naturally and accurately enforces the cardinality between SITE and CLAN/GAME/TEAM.

Your other rule that SITE must be only one of CLAN, GAME or TEAM would have to be enforced with a partitioning attribute on SITE, e.g.: SITE.subtype_code.

You would need either application logic or a trigger to enforce consistency between the partitioning attribute and the existence of a record in either CLAN, GAME or TEAM.

Answer 3

Two variations on what you name "idea 1":

Site
  site_id   primary key
  + additional site properties
Clan
  clan_id   primary key, foreign key site(site_id)
  + additional clan properties
Game
  game_id   primary key, foreign key site(site_id)
  clan_id   foreign key clan(clan_id)
    unique key (clan_id, game_id)
  + additional game properties
Team
  team_id   primary key, foreign key site(site_id)
  clan_id
  game_id
    foreign key (clan_id, game_id)
      references game(clan_id, game_id)
  + additional team properties

If you want to be finding easily what type every site is (without searching all 3 subtype tables):

Site
  site_id   
  site_type
    primary key (site_id, site_type)
    check site_type IN ('C', 'G', 'T')
  + additional site properties
Clan
  clan_id
  site_type    default 'C'
    primary key (clan_id, site_type)
    foreign key (clan_id, site_type)
      references site(site_id, site_type)
  + additional clan properties
Game
  game_id   
  site_type    default 'G'
    primary key (game_id, site_type)
    foreign key (game_id, site_type)
      references site(site_id, site_type)
  clan_id   foreign key clan(clan_id)
    unique key (clan_id, game_id)
  + additional game properties
Team
  team_id   
  site_type    default 'T'
    primary key (team_id, site_type)
    foreign key (team_id, site_type)
      references site(site_id, site_type)
  clan_id
  game_id
    foreign key (clan_id, game_id)
      references game(clan_id, game_id)
    unique key (clan_id, game_id, team_id)
  + additional team properties