SQL Validation Limit Without UDF

Question

I have the following (dummy) tables:

╔════════════════════╗ ╔════════════════════╗ ║ Course ║ ║ Person ║ ╠══════╦═════════════╣ ╠══════╦═════════════╣ ║ ID ║ int ║ ║ ID ║ int ║ ║ Name ║ varchar(50) ║ ║ Name ║ varchar(50) ║ ╚══════╩═════════════╝ ╚══════╩═════════════╝ ╔════════════════════╗ ╔═════════════════════╗ ║ Occupation ║ ║ B_Occupation_Person ║ ╠══════╦═════════════╣ ╠══════════════╦══════╣ ║ ID ║ int ║ ║ Person_ID ║ int ║ ║ Name ║ varchar(50) ║ ║ Ocupation_ID ║ int ║ ╚══════╩═════════════╝ ╚══════════════╩══════╝ ╔═════════════════╗ ║ B_Course_Person ║ ╠═══════════╦═════╣ ║ Course_ID ║ int ║ ║ Person_ID ║ int ║ ╚═══════════╩═════╝ 

There are two rows in the Occupation table: Student and Teacher .

The B_Occupation_Person binding B_Occupation_Person allows me to provide all people with a profession, and the B_Course_Person binding B_Course_Person allows me to associate the teacher with the course.

My problem is that I would like B_Course_Person to contain only teachers.

My first idea was to add a validation constraint to this table, but I can only do this with UDF to get the person occupation from the B_Occupation_Person table. And from what I read here , it is bad to use UDF in a control constraint.

My second idea was to add an Occupation column to the B_Course_Person table, but then I get data redundancy ...

What is the best way here?

Thanks,

Answer 1

If you have a "type" column in your face table to distinguish students from teachers (which, for example, is impossible if a person can be both), you can include this type column in the primary key, and then restrict the foreign key reference table for teachers:

 create table person ( id integer not null, person_type varchar(10) not null, name varchar(100), constraint pk_person primary key (id, person_type), constraint type_check check (person_type in ('student', 'teacher')) ); create table b_occupation_person ( occupation_id integer not null, person_id integer not null, person_type varchar(10) not null, constraint fk_occupation_person foreign key (person_id, person_type) references person (id, person_type), constraint type_check check (person_type = 'teacher') ); 

person_type redundant in b_occupation_person , but as far as I can tell, this is the only way to create this type of restriction in a declarative way.

Due to the foreign key and validation constraint, it is not possible to insert anything other than a teacher into b_occupation_person .

But then again: this only works if you can really distinguish teachers from students (and if the teacher cannot be a student).

If you need a teacher for the student (and you don’t have a "person_type"), you can think of a teacher table, which simply refers to a person table:

 create table person ( id integer not null primary key, name varchar(100) ); create table teacher ( person_id integer not null primary key, foreign key (person_id) references person (id) ); create table b_occupation_person ( occupation_id integer not null, teacher_id integer not null, foreign key (teacher_id) references teacher (person_id) ); 

The disadvantage of this is that the person who is the teacher must be inserted twice (once per person, once per teacher).

In PostgreSQL, you can take advantage of table inheritance and define teachers for human inheritance. Thus, any insertion into a teacher would automatically create a person (so you do not need to insert such a person twice).

Answer 2

Your question here is a form of business logic. You have not only the problem that b_course_person contains only teachers at the time of insertion, but also that the data remains consistent - that is, if a person stops becoming a teacher, what happens to the data in b_course_person, then?

Therefore, you must implement this logic in code; or as a stored procedure that represents your interface to the database and ensures that all changes to the data result in a consistent, valid set of data or further down your application tiers at the business logic level.