MySQL: Inheritance

Question

I read about an inheritance feature in PostgreSQL that seemed pretty neat. Unfortunately I am forced to use MySQL. How would you do something similar in a clean way?

Say you for example had the two following classes that you want to work with:

User
 ˪ Id
 ˪ Name
 ˪ Password

Employee : User
 ˪ Pay

How would you store those in a MySQL database so that they can be fetched from for example PHP in a fairly clean way? I need to for example be able to fetch all users including employees, only employees, find if a user is also an employee, et cetera.

Looking for general advice and not a specific solution to this case. I for example see that I could simply add a nullable Pay column to a Users table, but I don't really like that idea as it would quickly become very messy if more sub-classes were needed.

Answer 1

Add a field to Employee which is a foreign key to User. You'd still have to perform a join across the tables, but it's better than nothing.