super-subtype inheritance in access

alexm877
alexm877 used Ask the Experts™
on
I want to implement inheritance from a supertype table to a subtype. I know that is in the logical model.
Creating a table for each supertype and subtype and using the subtype's primary key as the primary key of the subtypes the inheritance is implemented BUT it is an overllaped one. What about created a disjoint inheritance? suppose you have an EMPLOYEE supertype, and HOURLY , SALARIED as it's two subclasses.
EMPLOYEE table has employeeID, name, phone
HOURLY table has employeeID, hoursworked
SALARIED table has employeeID, sallary

this model works fine for an overlapped relation(e.g. an employee can be both Hourly-paid and Sallary-paid). But if I want to have a disjoint relation(e.g. an employee can be either HOURLY or SALARIED, but cannot be both).
how can this be implemented in Access?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Commented:
The database won't support this type of relation, but there are multiple ways to solve this:
1) Type field solution
EMPLOYEE table has employeeID, name, phone, TypeSalary
HOURLY table has employeeID, hoursworked
SALARIED table has employeeID, sallary

By adding the type in the employee you can make only one table linked to hat employee. Additionally you could add the field also to the two other tables with the fixed value, thus enabling a FK relation that will guard both TypeSalary and employeeID.

2) One row solution
EMPLOYEE table has employeeID, name, phone
PayEmployee table has employeeID, hoursworked, sallary
Now only the fill of hoursworked OR sallary has to be controlled from the application.
It could also be used in combination with the TypeSalary field to make the one or the other field visible after the selection.
I assumed there will be multiple rows e.g. monthly, otherwise you could place all fields in just one employee row....

Nic;o)


Author

Commented:
Thanks for your answer, but could you explain me something more: suppose I have more than two types of inheritance e.g.

supertype tableA with attributes tableAID, B, C, D
subclass tableB with attr. tableAID, E,F,G
subclass tableC with attr. tableAID, H,J
subclass tableD with attr. tableAID, K,L,M,N,O,P
subclass tableE with attr. tableAID, O

now let's say to add the attribute type in the supertype. Do you mean that I have to add this attribute to all of the subtypes as a FK? what do you mean with a fixed value?

Commented:
Basically you want to have an "exclusive" relation between the main table and only one of the sub's.

By using an additional "type" field in both main and the subs you'll get:
Main table content:
tableAID, Type
1, SubA
2, SubA
3, SubB

SubtableA content:
tableAID, FixedType
1, SubA
2, SubA

SubtableB content:
tableAID, FixedType
3, SubB

As you can see, each subtable will have the FixedType field, always with the value corresponding to their type.
This will allow the creation of a double relation between main and sub table.

Clearer ?

Nic;o)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial