1

I am designing a database and as i do not have much experience in this subject, i am faced with a problem which i do not know how to go about solving.

In my conceptual model i have an object known as "Vehicle" which the customer orders and the stock system monitors. This supertype has two subtypes "Motorcar" and "Motorcycle". The user can order one or the other or even both.

Now that i am at the logical design stage, i need to know how i can have the system allow for two different types of products. The problem i have is that if i put each of the objects separate attributes into the same relation, then i will have columns that are of no use to some objects.

For example, if i just have a generic table holding both "Motorcars" and "Motorcycles" which i call "Vehicles" and all of their attributes, the cars will not need some of the motorcycle attributes and the motorcycle will not need all of the car attributes.

Is there a way to solve this issue?

Mike Smith
  • 11
  • 1
  • 1
    possible duplicate of [Something like inheritance in database design](http://stackoverflow.com/questions/554522/something-like-inheritance-in-database-design) – Merlyn Morgan-Graham Aug 14 '11 at 02:20

1 Answers1

3

The decision will need to be guided by the amount of shared information. I would start by identifying all the attributes and the rules about them.

If the majority of information is shared, you might not split into multiple tables. On the other hand, you can always split tables and then join into a view for ease of use.

For instance, you might have a vehicle table with only share information, and then a motorcar table with a foreign key to the vehicles table and a motorcycle table with a foreign key to the vehicles table. There is a certain difficulty ensuring that you don't have a motorocar row AND a motorcycle row referring to the same vehicle, and so there are other possibilities to mitigate that - but all that is unnecessary if the majority of information is common, you just have unused columns in a single vehicles table. You can even enforce with constraints to ensure that columns are NULL for types where they should not be filled in.

Cade Roux
  • 88,164
  • 40
  • 182
  • 265
  • Agreed. The purist approach is to use a VEHICLE table for all of the shared attributes plus a partitioning attribute and MOTORCAR and MOTORCYCLE tables for the distinct attributes. You can also have views (MOTORCAR_VIEW and MOTORCYCLE_VIEW?) that join the supertype and subtypes to give you a complete picture of any instance of one type or the other. You could also model it this way as your conceptual model but flatten it to a single table in your physical model. Using constraints driven by your partitioning attribute will keep your physical tables clean. – Joel Brown Aug 14 '11 at 12:06