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.

Ignacio Vazquez-Abrams · Accepted Answer · 2010-03-08 16:52:47Z

2

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.

answered Mar 8, 2010 at 16:52

Ignacio Vazquez-Abrams

804k160 gold badges1.4k silver badges1.4k bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Svish Over a year ago

So it would sort of be a 1-1 relationship and the Employees table would have a user_id column or something like that?

p.marino Over a year ago

Exact. You could also wrap up the join in a view so that - at least when you read/display your data the two (or more) tables look like one single table to your code.

Collectives™ on Stack Overflow

MySQL: Inheritance

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related