4

What would be the best way to design 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 design this database?

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

So I would need to have cars, fuels, and motor_oils tables in my database. Now since fuels and motor_oils have some similar properties like price, date_produced etc. Would it be smart for me to create another table called lets say products?

When using OOP language I would have Product entity, Fuel and MotorOils would extend Product and this way have properties defined in Product.

How would I design this in Mysql database? Is there such thing as inheritance in MySQL? I know that relation database does not have inheritance, and that there is a way to mimic this in MySQL, just need someone to explain better how is this done?

Improve this question
3
  • 1
    All you can (and should) do is add foreign keys in the fuel and motor_oil tables that refer to the product in the product table. Seeing as, inside the database, no actual inheritance needs to be defined, there's not much else you should want to do. Commented Nov 16, 2012 at 23:50
  • No idea why this was voted down - this is a legitime question. The right database design doesn't get enough attention in many projects out there Commented Nov 17, 2012 at 9:41
  • @Patrickdev thanks! Now the problem is, how would I wrote this in my OOP language, lets say PHP. Would I have Car, Fuel, MotorOil, Products Entities? Or should I ignore Products in Entities? Write this as an answer ;) Commented Nov 17, 2012 at 13:22

2 Answers 2

Reset to default
1

You could have a products table and a foreign key in each, fuel and motor oils linking to the products table in order to have some kind of inheritance.

Many to many relationships you create by defining a mapping table with a foreign between the entities.

Given you have cars and fuels you could create a table carsFuelsMap which has two foreign key fields morors_id and fuels_id

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

1 Comment

how do you prevent a product from being both a fuel and a motor oil at the same time in that solution?
1

You can simulate the delegation part of OOP in SQL using a one-to-one foreign key. So for instance a Fuel entry would have a foreign key to a Product that would identify all the shared values.

As for linking cars and fuels, this is actually a many-to-many relationship. The traditional way would use a CarFuel table that has one row for each valid combination of car and fuel, but as you've delegated parts of both Fuel and MotorOil to a Product, you probably want to use a CarProduct table, and if you only need fuels you can then join to the Fuel table.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.