1

I've been stuck on this for a very long time, tried different designs, but something is off always. I am designing a database for an art school / dance or fitness center kind of business. I am just trying to design the entities relation and info. Suppose we have person class - consisting of the following types 'student','guardian','teacher'. Each person could be of more than one type. Minors would have a guardian (parent, could have multiple) to hold the contact info , handle enrollments, etc. Some adults could also have a guardian (say an elderly or a PWD), a teacher could also be a student of another class / service.

I have been breaking my head of how to set this up (in particularly, the contact info) without breaking the normal forms. The last attempt I have is in the diagram below. I don't know where to tie the contact info, and the whole thing doesn't seem right.

Any help / guidance is appreciated.

enter image description here

B-and-P
  • 1,693
  • 10
  • 26

2 Answers2

1

I think you're overabstracting in your model. Implementing your different roles as concrete types, rather than generically, will allow you to specify unique attributes, relationships and constraints per role.

Consider the following table diagram:

School table diagram

I modeled teacher and student as concrete subtypes of person. They can overlap, i.e. a person can be both types. When a student is recorded, a student_number is required. When a teacher is recorded, an employee_number is recorded. I added those attributes just to indicate subtype-specific attributes.

Guardian is modeled as a many-to-many relationship between persons. This can create recursive associations. Depending on how you want to use guardians, the relationship can be further refined.

I added contact info on person. This means students can have different contact info from their guardians, you can choose what to use in your queries. One limitation is that each person can only have one address, phone, mobile and email; you could consider supporting multiple of each.

A class has a name and a teacher, and there's a many-to-many relationship (student_class) between class and student.

One overall limitation to keep in mind is that this model doesn't address time. Invariably, systems like these have to keep track of classes per term, semester or year, student registrations per year, etc.

reaanb
  • 9,806
  • 2
  • 23
  • 37
  • Thank you. I will try and implement your example. Yes, this is a small portion of the model, I haven't tackled schedules, attendance, payments yet. – B-and-P Jul 13 '17 at 08:25
0

The reason this is difficult is that the relational database model doesn't neatly support inheritance. It certainly doesn't support multiple inheritance.

You can find many questions on SO on this topic; this one is a good place to start.

@reaanb's answer is an example of "one table per subclass". It's a good model, and gives you a clear schema - but it means your application has to do the logic mapping "person" to all its possible subclasses, and then get the data attributes.

In short - there is no clean solution. Try to be as consistent as possible, and make it as clean as you can, but don't expect perfection.

Neville Kuyt
  • 29,247
  • 1
  • 37
  • 52
  • Thank you for the input, much appreciated – B-and-P Jul 13 '17 at 08:22
  • The relational model doesn't include table inheritance but it doesn't exclude it either. The RM addresses logical modeling, but inheritance would apply to the physical implementation of tables. It might save a bit of effort in applications/queries but it would further aggravate the conflation of data modeling with object-oriented programming, so I'm not sure if it would be a good idea. I'd be more excited about database inheritance than table inheritance. – reaanb Jul 13 '17 at 10:40