Entity Relationship Diagram. How does the IS A rel

2020-05-26 09:42发布

My drawing of a simple ER diagram

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.

4条回答
Evening l夕情丶
2楼-- · 2020-05-26 09:56

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.

查看更多
戒情不戒烟
3楼-- · 2020-05-26 10:00

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.

查看更多
放荡不羁爱自由
4楼-- · 2020-05-26 10:01

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.

查看更多
Animai°情兽
5楼-- · 2020-05-26 10:06

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

查看更多
登录 后发表回答