I have a base class (Person) and a subclass (Client) where Client has some additional properties beyond what Person tracks.
My question is what’s best practice for setting up the database table(s)? One table that combines all the fields for Person and Client? Or a Person table and a Client table? Or something else entirely (no clue what that would be, though)? I can see in my head (dis)advantages to both ways, but never having been down this road before I don’t want to hit that “bridge closed” sign at 70mph in the middle of the night.
The app is multi-user, LAN based, using MySQL for the db. Initial usage projections are only 5-10 users and not much of a load on the db.
I appreciate any wisdom y’all might be willing to share.
first of… Is a person and a client the “same”
Or can you have a “person” who is not a client… or for that matter a client that is not a person?
And can you have a person who can be a member of multiple clients?
Answers to those questions will dictate the direction to proceed. And don’t think of what it is today, but what it might be in the future (design any scalablity into the schema now instead of scrapping and doing it over again later)
A client is always a person, but a person is not always a client. There are also other subclasses from person - guardian, counselor, mentor, tutor, etc. Each type of person needs specific info tracked that the others don’t. But they all have a name, address, contact info, etc which is what Person tracks. So… the Person class is what is common among all the other subclasses.
It is possible to have overlap between person types, for example a mentor and a tutor could be the same person, but in reality it probably won’t happen very often.
I thought I was thinking about what it might be in the future. If in the future it was decided that the date of birth needs to be tracked, I could just add it to the Person class, and then it’s there for all the subclasses. That’s the power of subclasses, right?
So, again, how to store the info in the database?
Person info in the Person table, Client-specific info in the Client table, Guardian-specific info in the Guardian table, etc, with a FK in each of the subclass tables back to the Person table?
Or all fields for all subclasses in the Person table, with the subclass tables simply being an FK to the Person table (i.e. - the Client table is simply a list of Person PKs for whomever is a client)?