One to one relationship or another approach? 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 -&lt; Game -&lt; 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 &amp; 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 &amp; 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. :)
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.