sql server - How can I enforce this relationship? (Formerley: Is there a name for this type of database structure?) -

update 05/15/2013 understanding of problem has changed such "title" doesn't match anymore. should close , start new post? (what proper way handle edit in stackoverflow community?).

with following basic table structure, how can enforce relationship user can member of group, if user , group both related same tenant?

entities:

• tenants:

  • id
  • name

• user

  • id
  • fktenantid
  • name

• groups

  • id
  • fktenantid
  • name

• usersingroup

  • fkuserid
  • fkgroupid

for example:

i add 2 tenants:

id: name

1: tenanta

2: tenantb

i add 1 group each tenant:

id: tenantid: name

1: 1: groupa

2: 2: groupb

i add 1 user tenanta having id of 1:

id: tenantid: username

1: 1: usera

how can restrict can not add usera groupb.

if create table usersingroups columns fkuserid, fkgroupid, can't think of way constrain record 1:2 considered invalid.

thanks!

steve

with further research found post answers mine.

composite foreign key multiple related tables