mercredi 30 septembre 2015

Best database design for multiple entity types

I'm working on a web app and I have to design it's database. There's a part that didn't come very straightforward to me, so after some thinking and research I came with multiple ideas. Still neither seems completely suitable, so I'm not sure which one to implement and why.

The simplified problem looks as follows: I have a table Teacher. There are 2 types of teachers, according to the relations with their Fields and Subjects:

  1. A Teacher that's related to a Field, the Field is obligatory related to a Category
  2. A Teacher that's not related to a Field, but directly to a Category

My initial idea was to have two nullable foreign keys, one to the table Field, and the other to the table Category. But in this case, how can I make sure that exactly one is null, and the other one is not?

The other idea is to create a hierarchy, with two types of Teacher tables derived from the table Teacher (is-a relation), but I couldn't find any useful tutorial on this.

I'm developing the app using Django with SQLite db

Aucun commentaire:

Enregistrer un commentaire