Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
    
U mean that every recode in every table has one site or 3 table together have one site? –  john.locke Dec 23 '11 at 10:46
1  
A clan can have a site, so can a game and a team, but they cannot share a single site. Hope that makes sense. –  Mister Dai Dec 23 '11 at 10:52
    
I've just stumbled across a response by the mighty @BillKarwin stackoverflow.com/questions/922184/… on a similar topic. Looks like there are three "proper" approaches: Exclusive Arcs (multiple foreign keys), Reverse the relationship (not sure if that makes sense for this) or a Supertable (similar to the UUID idea). –  Mister Dai Dec 23 '11 at 10:55
add comment

3 Answers

up vote 3 down vote accepted

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.

share|improve this answer
    
That idea sits pretty well with me, but my only stumbling block comes when I want to find out what "type" a site is. I think have to check three tables for the site id. Maybe I'm trying too hard to do this the "right" way and should just settle for a "polymorphic association"... my head hurts :/ –  Mister Dai Dec 23 '11 at 11:40
    
yes @MisterDai U have to check three tables for the site id, but if you join tables and just fetch the site_id from those three table, the result will be fast. –  john.locke Dec 23 '11 at 11:59
add comment

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.

share|improve this answer
add comment

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
share|improve this answer
    
Thanks ypercube. Having the site type flag in the site table would make life easier. I'll probably be lazy loading records so it'd save queries to each table. Thanks for the help :) –  Mister Dai Jan 11 '12 at 15:09
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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