How do I relate requesters of different type?

Question

Users request materials. There are three types of requesters: person, department, and the supplier supplying the materials themselves. Supplier object needs to be related as the supplier as well.

In Request table there is a RequestedByID foreign key. But the requester has such a different structure for each type that it requires denormalisation if it were made a single table. How do I handle this?

Structure:

• Request (RequestID, RequesterID)
• Department (DepartmentID, DepField1, DepField2)
• Person (PersonID, PersonField1, PersonField2)
• Supplier (SupplierID, SuppFiel1, SuppField2)

Answer 1

You need what is in ER modeling know as inheritance (aka. category, subtype, generalization hierarchy etc.), something like this:

This way, it's easy to have different fields and FKs per requester kind, while still having only one REQUEST table. Essentially, you can varry the requester without being forced to also vary the request.

There are generally 3 ways to represent inheritance in the physical database. What you have tried is essentially the strategy #1 (merging all classes in single table), but I'd recommend strategy #3 (every class in separate table).