Entity Relationship Diagram. How does the IS A relationship translate into tables?

Question

I was simply wondering, how an ISA relationship in an ER diagram would translate into tables in a database.

Would there be 3 tables? One for person, one for student, and one for Teacher?

Or would there be 2 tables? One for student, and one for teacher, with each entity having the attributes of person + their own?

Or would there be one table with all 4 attributes and some of the squares in the table being null depending on whether it was a student or teacher in the row?

NOTE: I forgot to add this, but there is full coverage for the ISA relationship, so a person must be either a studen or a teacher.

Galvanism

Answer 1

Assuming the relationship is mandatory (as you said, a person has to be a student or a teacher) and disjoint (a person is either a student or a teacher, but not both), the best solution is with 2 tables, one for students and one for teachers.

If the participation is instead optional (which is not your case, but let's put it for completeness), then the 3 tables option is the way to go, with a Person(PersonID, Name) table and then the two other tables which will reference the Person table, e.g. Student(PersonID, GPA), with PersonID being PK and FK referencing Person(PersonID).

The 1 table option is probably not the best way here, and it will produce several records with null values (if a person is a student, the teacher-only attributes will be null and vice-versa).

If the disjointness is different, then it's a different story.

Mountie

Can the attribute PersonID be both the primary key and the foreign key in that table Student? – Commodus

@Commodus yes, that's perfectly fine for the PK attribute to be also a FK referencing another table (and in this case it's needed) – Mountie

If the Person-entity contains many common attributes for Student and Teacher, is it still a good idea to divide it into two tables? You would have duplicate attributes? I'm thinking to keep the Person-table, and have a non-nullable reference to it from student and teacher. – Fanchette

It depends on whether the participation is mandatory (every person has to be a student or a teacher) or not, and whether it's disjoint (either student or teacher, not both) or not. With the assumptions of the first paragraph, you don't have duplicate attributes. In general the extra table also means more joins every time you need attributes from the Person-table – Mountie

what is the triangle called? – Knobkerrie

Answer 2

there are 4 options you can use to map this into an ER,

option 1

• Person(SIN,Name)
• Student(SIN,GPA)
• Teacher(SIN,Salary)

option 2 Since this is a covering relationship, option 2 is not a good match.

• Student(SIN,Name,GPA)
• Teacher(SIN,Name,Salary)

option 3

• Person(SIN,Name,GPA,Salary,Person_Type) person type can be student/teacher

option 4

• Person(SIN,Name,GPA,Salary,Student,Teacher) Student and Teacher are bool type fields, it can be yes or no,a good option for overlapping

Since the sub classes don't have much attributes, option 3 and option 4 are better to map this into an ER

Jost

Answer 3

It depends entirely on the nature of the relationships.

IF the relationship between a Person and a Student is 1 to N (one to many), then the correct way would be to create a foreign key relationship, where the Student has a foreign key back to the Person's ID Primary Key Column. Same thing for the Person to Teacher relationship.

However, if the relationship is M to N (many to many), then you would want to create a separate table containing those relationships.

Assuming your ERD uses 1 to N relationships, your table structure ought to look something like this:

CREATE TABLE Person ( sin bigint, name text, PRIMARY KEY (sin) );

CREATE TABLE Student ( GPA float, fk_sin bigint, FOREIGN KEY (fk_sin) REFERENCES Person(sin) );

and follow the same example for the Teacher table. This approach will get you to 3rd Normal Form most of the time.

Febrile

I don't get your cardinality IF.... OP says "..a person must be either a student or a teacher". So in the Person to Student relationship there will be no 1 to N neither M to N but always 1 to 0..1 – Marable

Answer 4

This answer could have been a comment but I am putting it up here for the visibility.

I would like to address a few things that the chosen answer failed to address - and maybe elaborate a little on the consequences of the "two table" design.

The design of your database depends on the scope of your application and the type of relations and queries you want to perform. For example, if you have two types of users (student and teacher) and you have a lot of general relations that all users can part take, regardless of their type, then the two table design may end up with a lot of "duplicated" relations (like users can subscribe to different newsletters, instead of having one M2M relationship table between "users" and newsletters, you'll need two separate tables to represent that relation). This issue worsens if you have three different types of users instead of two, or if you have an extra layer of IsA in your hierarchy (part-time vs full-time students).

Another issue to consider - the types of constraints you want to implement. If your users have emails and you want to maintain a user-wide unique constraint on emails, then the implementation is trickier for a two-table design - you'll need to add an extra table for every unique constraint.

Another issue to consider is just duplications, generally. If you want to add a new common field to users, you'll need to do it multiple times. If you have unique constraints on that common field, you'll need a new table for that unique constraint too.

All of this is not to say that the two table design isn't the right solution. Depending on the type of relations, queries and features you are building, you may want to pick one design over the other, like is the case for most design decisions.

Shelves