Design to handle object inheritance

[chris.nebinger]

Okay, here's my question.

Taking the Northwind database, it is obvious that there are 3 types of
people. Customers, Employees, and Supplier contacts. Taking a OO
methodology, and considering inheritance, I would model that as a
Person class, with a Customer Class, Employee Class, and Supplier class
all inheriting from the Person class, possibly overloading some
methods/properites.


My question is: What is the best way to model that in a DB?

I see three possibilities.

A People table with all the fields that apply to each Person type.
Then, I could use a Employee table, with a PersonID and the remaining
employee types. And, if I need to map to different companies
(subsidiaries, etc.), I could use the Employee table as the many-many
relationship. This works, but then I have a Customer table, Employee
table, and Supplier table.

Another method that I've seen in models is to use 1 intermediary table
with:

PersonID
CustomerID
EmployeeID
SupplierID

The PersonID would be required, then either one of the remaining three
types of People.


Any thoughts?


Chris Nebinger

 

[Jeff Boyce]

Chris

I might quibble a point and suggest that there are People, and "n" roles. I
would opt for the Person/Entity table containing all common fields, and 1-1
subclass/subtype tables to handle the role-specific attributes.

Check the "linking 4 child tables ..." thread from yesterday in this
newsgroup. John V. offers pros & cons for either embedding the attributes
in the Person table or splitting them out. Either way requires some
additional handling.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[TC]

I agree with Jeff.

There are not really "three [different] types of people". There are
"people who have different roles". For example, the same person could
surely be a Customer in one context, an Employee in another context,
and a Supplier in a third context - all three simultanously. So you
have one person with three different roles - not three different types
of person.

IMHO, there is really no benefit in using OOP technologies at the
schema design stage of a traditional relational database product. Just
do the good ol' data modelling thing. Design the database schema
accordingly. *Then* you can worry about whether to encapsulate the
*functional processes* in objects, methods & properties. The design of
the *funcional processes* should not influence the design of the
reational schema, IMHO.

Remember: Access is not an *object database* - it's a traditional
relational database, in which you can, if you want to, write OOP *code*
to manipate the relational entities as if they *were* objects.

HTH,
TC (MVP Access)
http://tc2.atspace.com