I am building a PHP app that has 3 basic entity types: Coach, Student, Lesson, where coaches create digital lessons for students. I'm using MySQL and innoDB tables.
Requirements
I'm unsure what is the best DB schema to use given the requirements. Here are two options:
Option 1
User (PK id, user_type (coach or student), firstname, lastname, email, password, etc...)
Lesson (PK id, FK coach_user_id (ref: User.id), FK student_user_id (ref: User.id), lesson_name, etc…)
Pros:
- One user table
- Each user has a unique ID and email
- Makes login auth easy with single table
Cons:
- No validation of user_type when a coach or student User.id is recorded as a FK in the lesson table. This problem will reoccur in any new table where a coach or student User.id needs to be recorded as a FK.
- Potential polymorphism issues and the need to normalise down the track.
Option 2
Coach (PK id, firstname, lastname, email, password, etc...)
Student (PK id, firstname, lastname, email, password, etc...)
Lesson (PK id, FK coach_id (ref: Coach.id), FK student_id (ref: Student.id), lesson_name, lesson_text, etc…)
Pros:
- Normalised DB schema. Independent coach, students entity tables.
- No user type validation issues. Coach ID and student ID FK's point independently to Coach.id and Student.id respectively.
Cons:
- Coach and student can have the same ID. (This can be solved though with ID prefixes e.g. C1001, S1001)
- Coach and student can have the same email.
- Login auth involves querying two 2 tables for single login page, or creating 2 different login pages and auth request types.
I'm really torn which is the best way to go. Is there a better way to do this?
In my opinion, both of your approaches would work. The first one is more universal, and capable of fitting various currently unknown requirements . If you choose it, I'd recommend to add concept of Role to the model - "user_type" is a role, and one user can be associated with different roles [at the same time]. Also, "The Data Model Resource Book" by Len Silverston is a great resource .
However, you may not always want your schema to be too general. You listed pros and cons for 2 approaches on very low level; I think that practicability is more important than particular technical issues (which can be overcome ). I'd put it that way :
1) Pros :
Cons :
2) Pros :
Cons :
I hope it makes sense and helps you to make the right decision which fits your needs.
Option 1 looks better to me.
It will simplify your code when you don't care to distinguish students from coaches, and will be pretty much the same as option 2 if you want to distinguish them.
If you really need to validate the foreign keys you can use triggers to check if its a coach or not.
I'm not sure what you mean by "Potential polymorphism issues and the need to normalise down the track.".