Enforcing referential integrity

[Sheldon Slade]

Setup: I'm building a database to track enrollments in
software classes. Prospective students (or the companies
who want to train them) buy vouchers; each voucher is good
for one class. So I have a Vouchers table containing an
OwnerID field.

OwnerID corresponds to one of the following:
1. A record in the Companies table if the voucher belongs
to a company
2. A record in the Students table if the voucher belongs
to an individual

Difficulty: I'd like to enforce referential integrity and
use Cascade Update/Delete, but there will only be one
owner of a particular voucher (in either Commpanies or
Students). Is there a way to create an either/or enforced
relationship?

(Afterthought: In posting this, I'm thinking I might have
gone about it all wrong. I'm wondering now if I should
just create a "None" company and put all my individuals in
there, then trap the CompanyID whenever I'm trying to show
the "owner" of the voucher. Thoughts?)

TIA,
Sheldon

 

[Allen Browne]

Would you consider putting the individuals and companies all in the one
table?

This "client" table (where a client can be an individual or a company) would
have an IsCorporate (yes/no) field to distinugish whether the client is an
individual or a corporate entity.

 

[Sheldon Slade]

I thought about that originally, but I had a problem
making enrollments work - each voucher is good for one
enrollment (one _student_, one class), and when a company
buys vouchers they assign the vouchers to their employees
to be able to enroll. So I need a way for the students to
be attached to a particular company.

Sheldon

 

[Allen Browne]

Yes you do. So you would either have a StudentInCompany junction table (if
the student regularly stays with the company), or your Enrolment table would
have fields for EnrolleeID (the person attending the class), and PayerID
(who pays for this attendee).

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I thought about that originally, but I had a problem
making enrollments work - each voucher is good for one
enrollment (one _student_, one class), and when a company
buys vouchers they assign the vouchers to their employees
to be able to enroll. So I need a way for the students to
be attached to a particular company.

Sheldon