How do we implement an IS-A Relationship?
I did as Donnie suggested, but without the role field, because it complicates things. This is the final implementation:
DDL:
CREATE TABLE Employee (
ast VARCHAR(20) not null,
firstname VARCHAR(200) not null,
surname VARCHAR(200) not null,
...
PRIMARY KEY(ast)
);
CREATE TABLE Administrative (
employee_ast VARCHAR(20) not null REFERENCES Employee(ast),
PRIMARY KEY(employee_ast)
);
CREATE TABLE Technical (
employee_ast VARCHAR(20) not null REFERENCES Employee(ast),
...
PRIMARY KEY(employee_ast)
);
ER Diagram:
In this model there are no Employees of Generic Type. Here, an Employee can only be Administrative or Technical.
How to implement the IS-A relationship in Hibernate Spring Boot?
@Entity
public class Account {
@Id
private String id;
@OneToOne
private User user;
}
create the super class
@MappedSuperclass
public abstract class User {
@Id
private String id;
}
And extend it with your entities.
@Entity
public class Doctor extends User {
private String name;
private String email;
}
you need to create an (Absctract) class User and extend with
Patient and Doctor as well.You only need to define the id
field on the base entity.
You also have to provide getters and setters to all classes. I suggest using the lombok
annotations @Getter
and @Setter
Or you can map them one by one to the Account entity.
Hit me up if something isn't clear!
Entity Relationship Diagram. How does the IS A relationship translate into tables?
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.
How to implement one-to-one, one-to-many and many-to-many relationships while designing tables?
One-to-one: Use a foreign key to the referenced table:
student: student_id, first_name, last_name, address_id
address: address_id, address, city, zipcode, student_id # you can have a
# "link back" if you need
You must also put a unique constraint on the foreign key column (addess.student_id
) to prevent multiple rows in the child table (address
) from relating to the same row in the referenced table (student
).
One-to-many: Use a foreign key on the many side of the relationship linking back to the "one" side:
teachers: teacher_id, first_name, last_name # the "one" side
classes: class_id, class_name, teacher_id # the "many" side
Many-to-many: Use a junction table (example):
student: student_id, first_name, last_name
classes: class_id, name, teacher_id
student_classes: class_id, student_id # the junction table
Example queries:
-- Getting all students for a class:
SELECT s.student_id, last_name
FROM student_classes sc
INNER JOIN students s ON s.student_id = sc.student_id
WHERE sc.class_id = X
-- Getting all classes for a student:
SELECT c.class_id, name
FROM student_classes sc
INNER JOIN classes c ON c.class_id = sc.class_id
WHERE sc.student_id = Y
Related Topics
How to Use a Postgresql Triggers to Store Changes (SQL Statements and Row Changes)
Why Does SQL Server Keep Executing After Raiserror When Xact_Abort Is On
How to Group by Week in Postgresql
Make All Store Images the Base, Small and Thumbnail Images in Magento
Sql: How to to Sum Two Values from Different Tables
Where Col1,Col2 in (...) [SQL Subquery Using Composite Primary Key]
SQL Count Total Number of Rows Whilst Using Limit
Select Rows Not in Another Table, SQL Server Query
Bigquery SQL for Sliding Window Aggregate
How to Rename a Column in a Database Table Using SQL
Returning Month Name in SQL Server Query
What Free SQL Formatting Tools Exist
SQL to Query Text in Access with an Apostrophe in It
How to Write Subquery Inside the Outer Join Statement