Database design: OR relation

Question

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.

Answer 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

Answer 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.

Answer 3

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.