CODEWITHSUNDEEP
phpmysqlinheritance
Gapton
Gapton

Reputation: 2134

PHP MySQL table inheritance

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.

Upvotes: 0

Views: 1699

Answers (2)

Slawek
Slawek

Reputation: 583

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

Upvotes: 0

Vadim
Vadim

Reputation: 1372

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.

Upvotes: 3

Related Questions