MySQL Inheritance?

Question

What is the best way to create this MySQL database?

I have cars , fuels and motor oils . Each car can use any number of fuels and any number of motor oils . What would be the best way to create this database?

So, one car can have one or more fuels and one or more motor oils .

Therefore, I need to have the cars , fuels and motor_oils in my database. Now, since fuels and motor_oils have some similar properties like price, date_produced, etc. Would it be wise to create another table called products ?

When using OOP, I will have a Product entity, Fuel and MotorOils will expand Product , and this method has properties defined in Product .

How do I create this in a Mysql database? Is there such a thing as inheritance in MySQL? I know that a relationship database has no inheritance, and that there is a way to mimic this in MySQL, just need someone to better explain how this is done?

Answer 1

You may have a product table and a foreign key in each fuel and motor oil associated with the product table in order to have some kind of inheritance.

From many to many relationships that you create by defining a mapping table with an external element between objects.

Given that you have cars and fuel, you can create a carsFuelsMap table that has two foreign key fields morors_id and fuels_id

Answer 2

You can simulate the OOP delegation part in SQL using an individual foreign key. For example, a Fuel record would have a foreign key for Product that would identify all common values.

As for car-fuel binding, this is actually a many-to-many relationship. The traditional way uses the CarFuel table, which has one row for each valid combination of car and fuel, but since you delegated parts of both Fuel and MotorOil to Product , you probably want to use a CarProduct , and if you only need fuel, you can join the Fuel table.