Tables for parents and children - ensuring that children are fully prepared

Question

I am learning SQL and have some tables similar to the following:

Person (id*, name) Customer(id*, is_active, ...) Employee(id*, department_id, ...) 

(* indicates the primary key, which in the case of the Client and the Employee is both PC and FK for the Person)

Both Clients and Employees are types of Person, and I want to make sure that when the record is inserted into the Person, the record must also be inserted into the EITHER Customer or Employee, but NOT IT. A person cannot be both an employee and a client within the framework of this example.

I was told that a trigger would be useful for enforcing this restriction. Can someone explain the use of a trigger with this simple example?

Answer 1

Depending on what the DBMS supports and other factors, you can:

• Insert through a stored procedure that is implemented to ensure proper behavior.
• Or use a trigger in the view (which joins the supertype with a specific subtype) to make the view "updatable" and then insert it into the view.
• Or to ensure exclusivity and subtypes using purely declarative means, as described here .
• Or use an implementation strategy for inheritance other than “all classes in separate tables,” as mentioned here .

Answer 2

you can use INSTEAD OF triggers. Your question is fully covered in this MSDN article (Designing INSTEAD OF Triggers)

.

Answer 3

If you want to make sure that the person is either a Client or an Employee, you can create a column in the Person table called "Type", for example, and it can have the values ​​"Client" and "Employee" or "1", and "2" . And then paste the relevant information into the Customer or Employee table, as indicated in the comments above.