CODEWITHSUNDEEP
databasedatabase-design
Ant
Ant

Reputation: 31

Database design: OR relation

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.

Upvotes: 3

Views: 530

Answers (3)

Damir Sudarevic
Damir Sudarevic

Reputation: 22187

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

Upvotes: 7

Ben Hughes
Ben Hughes

Reputation: 1539

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.

Upvotes: 2

Mohnkuchenzentrale
Mohnkuchenzentrale

Reputation: 5885

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

Upvotes: 1

Related Questions