One to one relationship or another approach

database-design

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. 🙂

Best Answer

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.