Take the 2-minute tour ×

I am having a brain-cease on a data problem that I am in need of modeling. I will do my best to outline the tables, and relationships

users (basic user information name/etc)

hospitals (basic information about hospital name/etc)

user_id (page can be affiliated with a user)
hospital_id (page can be affiliated with a hospital)

Here is where the new data begins, and I am having an issue

groups (name of a group of pages)

groups_pages (linking table)

Now here is the tricky part .. a group can be 'owned' by either a user or hospital, but those pages arent necessarily affiliated with that user/hospital .. In addition, there is another type of entity (company) that can 'own' the group

When displaying the group, I will need to know of what type (user / hospital / company) the group is and be able to get the correct affiliated data (name, address, etc)

Im drawing a blank on how to link groups to its respective owner, knowing that its respective owner can be different.

share|improve this question
What is a page? –  Damir Sudarevic Jul 13 '12 at 16:11
add comment

4 Answers

up vote 0 down vote accepted
  • Party is a generic term for person or organization.
  • Keep all common fields (phone no, address..) in the Party table.
  • Person and Hospital should have only specific fields for the sub-type.
  • If the company has different set of columns from Hospital simply add it as another subtype.
  • If Hospital and company have same columns, rename the Hospital to more generic Organization
  • PartyType is the discriminator {P,H}

enter image description here

share|improve this answer
this is pretty similar to where I landed - esp. with naming hospitals/etc to a more generic term of organizations –  cgmckeever Jul 30 '12 at 13:26
add comment

You'd have to use some form of discriminator. Like adding a column with "owner_type", you could then use either an enum, a vchar, or just an int to represent what type of owner the column represents.

share|improve this answer
add comment

Here is a good tutorial on how to model inheritance in a database while maintaining a reasonable normal form and referential integrity.

Condensed version for you: Create another table, owners, and let it keep a minimal set of attributes (what users and hospitals have in common, maybe a full name, address, and of course an id). Users and hospitals will have their respective id columns that will simultaneously be their primary keys and also foreign keys referencing users.id. Give users the attributes that hospital's don't have and vice versa. Now each hospital is represented by two easily joined rows, one from owners and one from hospitals.

This allows you to reference users.id from groups.owner_id.

(There is also a simpler alternative where you create just one table for users and hospitals and put NULLs to all columns that do not apply to a particular row, but that quickly gets unwieldy.)

share|improve this answer
add comment
HospitalGroups(HospitalID, GroupID)
UserGroups(UserID, GroupID)
CompanyGroups(CompanyID, GroupID)


GroupPages(GroupID, PageID)

Pages(PageID, ...)

Would be the classic way.

The discriminator idea mentioned by @Robert would also work, but you lose referential integrity, so you need more code instead of more tables.

share|improve this answer
with the multiple group linking tables .. When selecting everythig from groups, how does one know if additional data is in the hospital/user/or company table? –  cgmckeever Jul 13 '12 at 14:09
Additional data about what? If it's about pages or groups, then it shouldn't be in those tables. –  Tony Hopkinson Jul 13 '12 at 18:05
add comment

Your Answer


