PHP MySQL table inheritance

Question

I am implementing two objects, say HotDog and Burger, and they are both subclass of Food.

The system I am working on will be used to create HotDog and Burger, potentially other types of Food, but never will it be used to created a generic "Food" object.

Food

class Food{
    //some fields
    protected $cooked_by;
    protected $use_before_date;
}

HotDog:

class HotDog extends Food{
    //some fields specicifc to HotDog
    private $sauage_type;
}

Burger

class Burger extends Food{
    //some fields specicifc to Burger 
    private $burger_type;
}

These models have a corresponding DB Table, namely Food, HotDog and Burger.

How if I want to select all HotDog cooked by a certain chief, currently I have to loop through every single row of HotDog like this:

SELECT food_id FROM hotdog

Then loop through the entire resultset, for each row doing:

SELECT * FROM food WHERE food_id = x AND cooked_by=chief0001

I hope you understand the structure I am trying to build here. Currently having to obtain all food_id for each HotDog row, then select from the food table one-by-one is a serious performance hit.

It would be nice to be able to just do :

SELECT * FROM hotdog WHERE cooked_by = chief0001

However as you can see, the current way of implementing the Models and the Tables does not allow me to do this. Surely I can add another column to the HotDog and Burger tables, however, should I need to query anything other than cooked_by, I will have to add yet another column to both tables.

Eventually, I will just add all the columns in the Food table into HotDog and Burger.

Is that the right way to do table inheritance ? I feel like something isn't right here, and there should be a cleaner and better solution. However I am pulling my hair trying to come up with a good way of overcoming this.

score 3 · Accepted Answer · answered Feb 25 '12 at 07:32

3

One common solution. You have specific fields in hotdog and burger tables, and all common fields in food table. Then you just JOIN tables to get all fields. Like:

SELECT f.*, h.* FROM food f JOIN hotdog h ON f.id = h.food_id
WHERE f.chief_id = chief0001

to get hotdogs.

answered Feb 25 '12 at 07:32

Vadim

1,336
2
10
10

Thanks! Now I feel like an idiot who doesn't know about SQL JOIN lol. Is JOIN operation generally quite slow? – Gapton Feb 27 '12 at 06:59
JOIN is essential part of SQL, so any good sql engine must be well optimized for this. Also make sure you have indexes by both fields used in ON clause (f.id and h.food_id in our case). It must work very well :) – Vadim Feb 27 '12 at 11:27
@Vadim This solution works very well for SQL queries, but what about if you need to do `Burger` POST operations or PUT operations involving both `Food` and `Burger` very often? Would it not hit the performance very hard as you need to update two tables every time? – Lorenzo Polidori Mar 19 '13 at 09:26
@Vadim As a general rule, Multiple Table Inheritance (MTI) is preferable if your models have plenty common attributes but at the same time plenty uncommon. If it is not the case, you should probably implement Single Table Inheritance (STI) and have them in the same table when they have little uncommon attributes or not implement any inheritance at all and keep them in separate tables when they have little common attributes.. See also: http://stackoverflow.com/questions/6073617/single-table-inheritance-or-class-table-inheritance – Lorenzo Polidori Mar 19 '13 at 10:13

score 0 · Answer 2 · answered Feb 25 '12 at 08:32

I normally use two scenarios for such problem.

a) Base table for all elements, with type_name field - which is string corresponding to concrete class name, and additional tables for concrete types

For your example:

food:
    id
    name
    creation_time
    type_name (hamburger|hotdog) 

hamburger:
    food_id
    with_cheese

hotdog:
    food_id
    with_salad

b) Base table for all elements, with type_name field, the same as above, and second table for storing values for concrete types

food:
    id
    name
    creation_time
    type_name (hamburger|hotdog) 

food_options:
    food_id
    option_name
    option_value

PHP MySQL table inheritance

2 Answers2