CODEWITHSUNDEEP
databasedatabase-design
cra
cra

Reputation: 127

How do I relate requesters of different type?

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:

Upvotes: 2

Views: 228

Answers (4)

Branko Dimitrijevic
Branko Dimitrijevic

Reputation: 52147

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

enter image description here

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

Upvotes: 2

bluelightning1
bluelightning1

Reputation: 289

What I like about Jack Radcliffe's thought is if you store them in a separate table or make the sql statement generic to handle any number passed in by the application, they can be expanded e.g. manufacture, entity, subsidiary, etc

However, you choose the expansion will entail overhead.

Upvotes: 0

user1494736
user1494736

Reputation: 2400

What Jack Radcliffe suggested is probably the best option. So I'd just add an alternative option:

You might also consider having 3 requests tables... One for ppl requests, one for suppliers requests, and one for departments requests... So you don't need to explicitly store the RequesterTypeID, since you can deduce it from the name of the table... You can then create the table Jack Radcliffe as a view, by "uniting" all the 3 individual tables...

Also, if you implement Jack Radcliffe approach, you can create 3 views to simulate the 3 tables I've mention... So then you can use whichever table/view is best for each situation, and if you want to change from approach A to B it's really easy too...

Upvotes: 0

jrad
jrad

Reputation: 3190

You could have two different IDs: RequesterID and RequesterTypeID. RequesterTypeID would just be 1, 2, or 3 for Person, Department, and Supplier, respectively, and RequesterTypeID paired with RequesterID would together make a multi-attribute primary key.

Upvotes: 0

Related Questions