Database design: How can you represent conditions in a relationship?

user_8582246 0
Viewed 222

There are situations where, according to a condition, a type of entity must be associated with another type of entity, for example:

  1. There is a car entity that can be of different types, it can be a public service car such as a cab or it can be a private car(family car).
  2. Depending on the type of car, you may have obligations(this is the way I represent the taxes on a car) to pay. If you have a private car you need to pay a tax year by year, but for example in some countries cabs or public cars must pay additional taxes to provide the service of transporting passengers in a certain city.
  3. Both types of car share certain obligations(taxes), that no matter what kind of car it is, private or public, it must pay. But each type of car can have its own obligation(taxes) types to pay.

Condition

Depending on the type of vehicle a type of taxes/obligations must be paid. If it is a private car you will relate the normal tax, but if it is a public car you will relate the normal tax, plus an additional one for the transport of passengers. how can I represent these kinds of conditions in a database model?

What I've tried

enter image description here

I'm not sure if it's the right way to represent this type of condition, but I'm using an intermediate table that I call obligation_type_of_car, which associates the type of car with the type of obligation. I'm also not sure what cardinalities to place from this intermediate table to the other tables.

update

enter image description here

database-design database
1 Answers

I would suggest you to map obligation to cartype, as it is tied to cartype.

obligationtype is not related to cartype. So, it is better to leave it from the cartype_obligation table. You can derive obligationtype from obligation table.

  • car

  • car_type

  • obligation

  • obligation_type

  • New table: Cartype_obligation

    • car_type_id
    • obligation_id
user_6349350
Related