3

I have a database with a "users" table containing data about my users. Each user is to be linked to a company or a college. I wish to have two separate tables "college" and "company" each with a field "ID". So how do I link each record in the users table to either a company or a college?

The basic thing is that I wish to establish an "OR" relationship in the database.

Ant
  • 31
  • 2

3 Answers3

7

You can use subtype/super-type relationship. Keep all common fields in the organization table. College and company tables contain only fields specific to those entities.

user_organization

Damir Sudarevic
  • 21,891
  • 3
  • 47
  • 71
  • This is a better design than the two by Ben Hughes IMO. If you add another table down the line, you don't have to create yet another FK column as in his second option, and it doesn't require multiple significations as Konerak rightly criticized in his first. – Dan May 24 '10 at 14:51
  • I'm unfamiliar with this notation. It looks to combine elements of Logic Data Modeling and Physical. @Ant, keep in mind that in a logical data model such as this, the OrgType inheritance could be physically manifested as a specifying attribute (a OrgType Column) in the Org table. – Stephanie Page May 24 '10 at 17:32
  • @Stephanie; the diagram is quite physical :) OrganizationType is simply a category classifier column. See category-symbol in your ERD tool. – Damir Sudarevic May 25 '10 at 13:55
  • So if it's all physical then the grey box headers are for tables and you'd create tables for Company and College? – Stephanie Page May 26 '10 at 16:31
2

You could use an 'institution' or 'organisation' lookup table, with a structure something like

InstitutionId[PK], InstitutionType, LookupKey

where LookupKey is the PK to either Company or College.

Or, 

InstitutionId[PK], CompanyId[FK], CollegeId[FK]

In both cases you link from user to institution, then onto Company and/or College.

I personally prefer the second option, because it allows you to easily validate the FK relationship and also allows (if applicable) for a user to be a member of a company and/or a college.

Ben Hughes
  • 1,539
  • 13
  • 20
  • 2
    While the first solution appears to be stricter in the 'OR' relationship, author is correct preferring the second option. One column with multiple significations is often bad design and not very extensible in the future (example: yet another signification is added but old applications do not know about it and need to be modified) – Konerak May 24 '10 at 06:57
  • +1 I agree with Ben and Konerak's preference - I'd never advocate solution #1, it will lead to messy data over time. Solution #2 works fine, and it's the only truly referentially "safe" way of doing this. – marc_s May 24 '10 at 07:12
1

I would create relationtables. UserCollege and UserCompany. This way you are even able to have users that are linked to both if needed in the future. If not you simply just create a relationrecord on one of the both

Mohnkuchenzentrale
  • 5,745
  • 4
  • 30
  • 41
  • No. If you want to, in the future relate a person to both college and company, you'd just change relationship in the Org-User (on Damir's model) to many-many. – Stephanie Page May 24 '10 at 17:29