2

I have a database structure issue I am looking for some opinions on.

Let's say there is a scenario where users will use an application to request materials.

There is the need to track who the requester is.

There are three possible "types" of requesters. An individual (Person), a Department, and the Supplier supplying the materials themselves.

In addition the Supplier object needs to be related as the Supplier as well.

So the idea is in the Request table there is a RequestedByID FK. But the related requester has such a different structure for the data for each that it would require a completely denormalized table to related back to if it were made just a single table (people have different properties than departments, and suppliers).

I have some ideas on how I might handle this but thought the SO community would have some great insight.

Thanks for any and all help.

EDIT:

pseudo structure:

Request

RequestID RequesterID

Department

DepartmentID DepField1 DepField2

Person

PersonID PersonField1 PersonField2

Supplier

SupplierID SuppFiel1 SuppField2

Department, Person, and Supplier all have separate tables because they differ in their properties quite a bit. But each of them can serve as the Requester of a Request (RequesterID). What is the best way to accomplish this without one (denormalized table) full of the different possible requesters?

Hope this helps. . .

cra
  • 127
  • 1
  • 9

4 Answers4

2

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

Community
  • 1
  • 1
Branko Dimitrijevic
  • 50,809
  • 10
  • 93
  • 167
  • This is also how giant business suites do it. Every person/company/department has a parent record in a single table. – jva Jul 19 '12 at 10:57
  • I like this approach and it's similar to one of the ideas I was kicking around. My question would be in this scenario you would store the Requester_ID in the subsequent Person, Department, Supplier tables OR would you have columns in the Requester table for Person_ID, Department_ID, Supplier_ID? – cra Jul 19 '12 at 18:51
  • @cra The first is the orthodox approach - you simply have FKs from children to parent, as denoted by the `FK1` in the diagram above. This allows a child-less parent. The second approach would not protect you from inserting a parent-less child. People usually think the child-less parent is a lesser evil and go for the first approach. You'd actually need a **combination** of both to protect from both, causing a cyclical dependency, requiring deferred constraints to resolve, which are not supported by all DBMSes. Even if supported, this is probably more trouble than it's worth. – Branko Dimitrijevic Jul 19 '12 at 23:05
0

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.

jrad
  • 3,172
  • 3
  • 22
  • 24
0

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

user1494736
  • 2,425
  • 16
  • 8
0

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.

bluelightning1
  • 279
  • 2
  • 6