1

I'm creating a table of ownerships for files. A file can be owned by either a single user (table Users) or a group of users (table UserGroups). I would like to make this whole relationship as one table, where each tuple is either a group's ownership or a user's ownership of a file.

Users: User_ID(PK), name, email
UserGroups: UserGrp_ID(PK), name, creator_ID(FK to Users)

TestGroupOwnerships: ???

I think it's possible to do both relationships as one table, but I'm not exactly sure how. It is also acceptable if Users or UserGroups tables need to change (still in the planning stage).

Thanks,

Alex

Raidil142
  • 137
  • 1
  • 10
  • possible duplicate of [Possible to do a MySQL foreign key to one of two possible tables?](http://stackoverflow.com/questions/441001/possible-to-do-a-mysql-foreign-key-to-one-of-two-possible-tables) – Conrad Frix Aug 26 '11 at 16:41
  • 1
    What you are trying to achieve is a polymorphic relationship. Have a look at an already answered question [here](http://stackoverflow.com/questions/441001/possible-to-do-a-mysql-foreign-key-to-one-of-two-possible-tables) – aziz punjani Aug 26 '11 at 16:40

3 Answers3

3

You could:

  • allow NULL on both FK columns
  • add a CHECK CONSTRAINT to ensure that one of them is non-null:
ALTER TABLE dbo.MyTable ADD CONSTRAINT
    CK_MyTable CHECK (Column1 IS NOT NULL OR Column2 IS NOT NULL)
GO
p.campbell
  • 98,673
  • 67
  • 256
  • 322
  • 1
    wouldn't that be and **OR**: `(Column1 IS NOT NULL OR Column2 IS NOT NULL)` – KM. Aug 26 '11 at 18:00
  • 1
    In my case what I actually need is either column1 or column2 is not null, but not both (XOR). So I think this will work: (Col1 IS NOT NULL OR col2 IS NOT NULL) AND (col1 IS NULL OR col2 IS NULL) – Raidil142 Aug 26 '11 at 19:43
1

enter image description here

Notes

  1. OwnerID = PartyID (OwnerId is a role name of PartyID in the File table).
  2. UserID = PartyID (UserID is a role name of PartyID in the User table.)
  3. GroupID = PartyID (GroupID is a role name of PartyID in the Group table.)
Damir Sudarevic
  • 21,891
  • 3
  • 47
  • 71
0

How about:

Have a table with (File,OwnerID (int),OwnerType(Char(1))

Where OwnerID can either be UserID or GropuID and OwnerType can either Be ('G' - Group or 'U' - User)

Or....

Have a table with (File,OwnerID,GroupID) where a touple that has OwnerID populated joins to the user table and a tuple with GroupID populated joins to the Groups table.

Icarus
  • 63,293
  • 14
  • 100
  • 115