The Implementation of ISA hierarchy

[Arman]

Dear Access-Programmers community,

I have come into a little confusion on how an ERD ISA hierarchy would be implemented within Access. While I do have a general idea and a possible solution to it, I am not quite sure whether the approach I take is the correct one.

As an example, I'd take the "Person -> Doctor OR Patient", made a quick ERD (as seen below) and attempted to implement it into Access, but I am not quite sure whether the implementation I did would be the correct one.

Access implementation:

 

[isladogs]

You could instead have 2 separate Doctor & Patient tables with a junction table DoctorPatient to include the PK fields from the other two tables

 

[Arman]

You could instead have 2 separate Doctor & Patient tables with a junction table DoctorPatient to include the PK fields from the other two tables

Wouldn't that require me to enter data in the Doctor / Patient table first and then the data for the junction table? In other words, DoctorID would be an AutoNumber (its own) and PatientID would be AutoNumber, so I eventually may end up with the same ID value in the junction table (where one comes from the Doctor table and the other from Patient table). The way you described it sounds more like a Many-To-Many connection.

 

[isladogs]

For clarity lets rename the junction table as Appointments

This junction table would have fields including:
ApptID - PK - autonumber
DoctorID - FK - number FK
PatientID - FK - number
ApptDateTime - datetime
Location - text ......etc

You would of course need to populate both Doctor & Patient tables first
But that's reality - you can't have an appointment without a patient or a doctor or indeed a place & time for that to happen

How do you intend to add appointments to your system?

 

[MarkK]

Looks like you are running into Object-relational impedance mismatch, which is the difficulty that occurs when you take an OOP inheritance model, and try to supply it with data using SQL tables. This is a problem without a solution, but if that is what is going on, I would stick with the first option you have, one Person table, and then the two distinct Doctor and Patient tables that would store the data for the inherited types.
hth
Mark

 

[Arman]

For clarity lets rename the junction table as Appointments

This junction table would have fields including:
ApptID - PK - autonumber
DoctorID - FK - number FK
PatientID - FK - number
ApptDateTime - datetime
Location - text ......etc

You would of course need to populate both Doctor & Patient tables first
But that's reality - you can't have an appointment without a patient or a doctor or indeed a place & time for that to happen

How do you intend to add appointments to your system?

What I basically tried to achieve is to enter person info into Persons entity / table. Then, depending on whether the person is a doctor or patient, I'd have some info about him / her specific to their status (doctor has EmploymentDate, Sallary while patients have "SomeAttribute") - only data that a doctor or patient have goes in their tables. At a given time, a person can be ONLY a doctor OR ONLY a patient. That's the whole point of the ISA hierarchy. There was no talk about appointments or anything else here.

 

[jdraw]

Here is a link to info on generalization/specialization that may offer more to consider. It isn't Access per se, but it does consider relational databases.

Here's a video by Dr. Soper. He deals with subtypes etc around the 45min mark. It might be helpful at least for concepts.

You mention IS-A relationship, but if you search for Supertype/subtype and Specialization/Generalization you will find info.

ErWin data modeller used to have a lot of info, but they dropped their free community edition and I have not followed them for the last year or so.

The TOAD data modeller doesn't support supertype/subtype in the same way and some on their discussion group said it was not required--you can always work around them with standard methods.

Here is a general approach (from older Info Mgmt article) when you have a logical data model and it has subtype.

[COLOR="RoyalBlue"][I]On the physical data model we can replace this subtype symbol with one of three options:
•	[U]Rolling down[/U]: Remove the supertype entity and copy all of the 
data elements and relationships from the supertype 
to each of the subtypes. 
•	[U]Rolling up[/U]: Remove the subtypes and copy all of the 
data elements and relationships from each subtype to the supertype.
 Also add a type code to distinguish the subtypes. 
•	[U]Identity[/U]: Convert the subtype symbol into a series of
 one-to-one relationships, connecting the supertype to each
 of the subtypes.[/I][/COLOR]

Additional info re subtype/supertype to physical database

 

[Arman]

Marking as done as the answers provided here were not really trying to answer whether "the implementation I did was a valid one" but instead linked to theoretical database videos that I am already familiar with or providing info totally unrelated to the question.

 

[jdraw]

Arman,

Your direct question

whether "the implementation I did was a valid one"

really depends on your situation and your tests and validation with your data. You know your situation and surrounding context and readers do not. If you have a solution that works for you then that is a valid solution.

Your initial post

While I do have a general idea and a possible solution to it, I am not quite sure whether the approach I take is the correct one.

suggests you were less certain of your solution and were seeking advice.

In any event, the material that Markk linked to shows that there really isn't a solution for physically implementing such inheritance into a relational database.
There are "work arounds" that have been suggested. I included links to articles and videos mainly for 2 reasons:
a) I have not been able to find physical Access implementations and examples dealing with sub/supertype, specification/generalization situations (IS-A relationships), and
b) I noted that you joined in Dec 2017 and had only a few posts and did not give us a full business context of your situation/environment.

If you have, or if you find, a good tutorial on the IS_A construct that goes from a plain English description to logical model and to a physical implementation showing the details, it would be an excellent candidate for the Sample Databases or Code Repository area.

Here is a link re inheritance options when converting to a physical model from logical model.

 

[nardimrk]

I did not know nothing about this scheme