Is this a bad database design?

Question

I have two tables, Teachers and Departments.

Teachers works at a department, so teacher table has a department_id foreign key.

Each department has one teacher as its chairman. Therefore, Department has a foreign key chairman referring to the Teachers table.

However, teachers table can contain teachers from many departments. But a chairman must be a teacher of that same department. So, it seems foreign key should refer to the teachers subset instead of whole table.

I have two questions.

Is it a bad design since invalid teacher can be put into Department table as head?
If not, how do I enforce this in the database?

How about a foreign key constraint on both columns? `ALTER TABLE dbo.Departments ADD CONSTRAINT FK_Chairman FOREIGN KEY(ID, Teacher_ID) REFERENCES dbo.Teachers(Department_ID, ID)` — russianmario, Oct 06 '15 at 20:23
I have two tables. "teachers" and departments. has foreign key to department, and department has a foreign key to teacher. there is not Library table — Anwar, Oct 06 '15 at 20:28
What kind of rdms system are you using? Some databases support constraints which are similar to keys but can employ lookups and other sql logic to return a valid or not valid indicator during and insert or update into a table. — Ross Bush, Oct 06 '15 at 20:34
Only ID column is primary key in teachers table. As far as I know , foreign key must refer to a primary key. And also, referring department id doesn't guarantee that a teacher with another department get assigned as a chairman. At least, I don't see it how — Anwar, Oct 06 '15 at 20:34
I looked to see if there was an equivalent to CHECK CONSTRAINT in MySQL and came across this so answer which might prove useful: http://stackoverflow.com/questions/2115497/check-constraint-in-mysql-is-not-working — Ross Bush, Oct 06 '15 at 20:38
@lrb Thanks. I have seen that question. I also did some trigger stuff. Only I want to know if there is more cleaner way to do this — Anwar, Oct 06 '15 at 20:48

score 2 · Answer 1 · answered Oct 06 '15 at 20:51

It is not a bad database design, because from a relational point of view, the relationships are fine.

Your requirement is an application concern, so it is okay to handle it at the application layer, meaning, to take pains in the application which makes use of this database to ensure that the chairman will always belong to the department that they are chairing.

But the question of whether relational databases are meant to enforce only rules about the natural relationships of entities, or whether they should also enforce business rules, is a philosophical one. You can try also enforcing business rules too, but you should know that not all business rules can be enforced by a database, or are practical to enforce by a database.

If you insist on going that path, then rlb's comment points to a decent solution. (Look for another comment in that post which points to another post which shows how to generate an error from within a trigger.)

Thanks. Your answer helped me to remove a major confusion. I always tried to enforce business rules on rdms! — Anwar, Oct 06 '15 at 21:05
When I say that it is a philosophical question, I mean that there is no definite answer to it. It is an open question. Others might successfully argue that you should enforce any relationship you can think of at the RDBMS. Listen to what others say, someone might bring better arguments for the opposite of what I suggested. — Mike Nakis, Oct 06 '15 at 21:07

Pavel Gatnar · Answer 2 · 2015-10-06T20:53:38.717

you should think about an alternative design: flag a teacher as chairman ('Y' for chairman and null for other) and make unique key on (department_id, chairman)

in oracle: create unique index chairman_uq_idx on teacher(department_id, nvl(chairman, teacher_id))

in other DBs: create unique index chairman_uq_idx on teacher(department_id, chairman) where chairman is not null

there are 2 simple reasons:

circular references among tables are always potential source of troubles
ORM subclassing - chairman is a specialization of teacher

Schwern · Accepted Answer · 2015-10-06T21:00:58.280

1

Your design makes a number of assumptions.

A teacher can only be in one department.
Each department can have only one chairman.
Chairman is the only position you'll want to store.
You don't want to know anything more about the chairman.
Only teachers can be chairmen.

If these assumptions hold true in realty you can solve your conundrum by taking advantage that a teacher can only be in one department. Move the "chairman" flag into the teacher table. That guarantees the chairman is a member of the department.

However, I think those assumptions won't survive an encounter with reality. If they don't it would involve significant restructuring of your schema and queries. I would restructure the schema to store the more generic staff and turn teacher into a department role. Staff and departments are in a many-to-many relationship. This relationship can be used to store the type of relationship as well as any further information.

CREATE TABLE staff (
    id    INTEGER PRIMARY KEY,
    name  TEXT NOT NULL
);

CREATE TABLE departments (
    id    INTEGER PRIMARY KEY,
    name  TEXT NOT NULL
);

CREATE TABLE department_roles (
    staff_id      INTEGER REFERENCES staff(id),
    department_id INTEGER REFERENCES departments(id),
    role          ENUM('chairman', 'teacher')
);

That layout guarantees a chairman is in their own department. Constraints on department_roles can enforce business logic like having one chairman per department, or only teachers can be chairmen, and they can be removed or changed later without affecting queries. It allows adding roles in the future. It allows adding more information to those roles. It allows non-teachers to hold department roles.

edited Oct 06 '15 at 21:00

answered Oct 06 '15 at 20:53

Schwern

153,029
25
195
336

1

It seems I needed a many to many. Thanks! assumptions 2,3,4 are true. But A teacher can take class of other department, but he only works (permanently) on a single department. and that's one info is needed. – Anwar Oct 06 '15 at 21:00
1

@Anwar Those assumptions are true *now*, what about later? The assumptions I listed are all business logic and not inherent properties of the data. Business logic should be enforced with constraints, not restructuring tables. Restructuring causes a query compatibility problem. – Schwern Oct 06 '15 at 21:02
1

Thanks. Your comment helped, and also the answer. :) – Anwar Oct 06 '15 at 21:08
1

this is the reason I prefer document oriented DBs for such small projects – Pavel Gatnar Oct 06 '15 at 21:11
I also need to store a teacher's designation, such as `Lecturer`, `Professor` etc. which other staff lacks. what will be your suggestion in this case – Anwar Oct 07 '15 at 09:27
1

@Anwar If it's an honorarium associated with the role, put it in a column in `department_roles`. If it's associated with the person (ie. "Doctor") put it in `staff`. If it carries with it different privileges within the department, it might be its own role. – Schwern Oct 07 '15 at 18:08

score 1 · Answer 4 · answered Oct 06 '15 at 21:04

As per the other posts, there are a number of ways of solving this problem, and that’s once you are 100% sure of the problem actually being solved (vis. @Schwern’s post). The following is perhaps a bit awkward, but I've used this kind of structure to good effect in similar circumstances.

Teacher table looks like:

TEACHER
TeacherId
DepartmentId

with constraints:

Primary key on {TeacherId}
Unique constraint on (TeacherId, DepartmentId}

The unique constraint is necessary for the following foreign key.

Department table looks like:

DEPARTMENT
DepartmentId
ChairmanId

with constraints:

Primary key on {DepartmentId}
Foreign key constraint on (ChairmanId, DepartmentId} into table TEACHER, against the unique constraint

You’d have to make at least one of ChairmanId or TEACHER.DepartmentId NULLable, as otherwise you’d never be able to load data.

Is this a bad database design?

4 Answers4