Inheritance in relational database

Asked

Viewed 8,955 times

13

one of the great challenges in developing object-oriented software is to abstract the data from its base to power-like objects better manipulate them, today the available ORM frameworks make this abstraction quite easy,the same problem is when we start modeling the system from objects and especially when we give hierarchy to them using heritages, how to represent these hierarchies in the relational database?

for example

class veiculo
class carro extends veiculo
class moto extends veiculo

What is the best way to represent these hierarchies in the relational database, a table for each type? same vehicle being an abstract class?

database oop modeling pattern-design inheritance

  • It depends on what data you have on each object (car and motorcycle). Considering a minimum data set, a single table serves (probably a single class to represent both as well).

  • This hierarchy would have to be justified by the domain, by the business rules that your system meets. If you will register traffic violations of these vehicles, you do not need classes to differentiate them, you can have a vehicle type attribute (car, motorcycle, bus...). If the system is going to serve a tire shop, you don’t even need a "vehicle" or "car" or "motorcycle" class, because what matters is the type of vehicle the tire meets (the "vehicle type" attribute on the tire). Defining conceptual modeling well, relational database modeling emerges.

  • @Caffé yes you are right, and I know there are cases and cases, but in case this hierarchy is justified, my question is what is the best representation of this hierarchy in a relational data group system?

  • In this case, once the hierarchy is desired by the domain, I refer to the first part of my first comment: Depende de que dados você tem em cada objeto (carro e moto). Considerando um conjunto mínimo de dados, uma única tabela serve.. That is, just as more details were needed to decide the hierarchy, these same details are now needed to decide the design of the database.

1 answer

12


There are several ways to map the inheritance relationship in the bank. The best strategy will depend on the situation (types of queries that will be performed against the data, amount of common fields vs specific fields, hierarchy size, amount of data, etc).

1. Table by entity

Each table will contain not only the data of the daughter class but also the data of the parent class. This allows "independent" queries between car and motorbikes to be simpler and faster. However queries seeking data from more than one entity require the use of queries with union all (which is problematic for a number of reasons).

2. Single table + breakdown column

A single table will contain all data of all entities. To differentiate between cars and motorcycles we use a discriminating column. This allows "generic" queries (i.e., on all vehicles) to be faster. It is especially appropriate when there are few specific car and bike attributes (thus avoiding a lot of null values).

3. Main table + daughter table with FK for main table

This model is appropriate when normalization is essential and the daughter tables have many columns. Also this strategy is especially interesting to work with multiple inheritance (an entity can be of various types in the hierarchy simultaneously). The weakness of this modeling is that queries require join, which makes the code complex and brings problems of performance as the amount of data and the depth of the hierarchy grows.

4. Joint strategies

Nothing prevents you from modelling part of your hierarchy according to one strategy and part according to another. For example, you can have a main vehicle table with the data common to all vehicles (speed, size, weight, etc.), but have unique tables for each type of vehicle (land vehicles, water vehicles, flying vehicles, etc.).

All strategies have their advantages and disadvantages; OO and relational representations are sufficiently different for several mapping problems to exist (see Object-Lational impedance Ismatch). If your problem cannot be adequately handled by any of the strategies, there are also solutions with Object oriented database, Nosql (see Schemaless Data Structures of Martin Fowler), indexes, etc. Each with its respective strengths and weaknesses.

  • optimal analogy of the solutions, in the third solution that in the case could involve multiple inheritances there is no way outside the programming to ensure that the child classes do not have the same relationship (FK) with the right parent class?

  • Tuyoshi Vinicius, has yes. Depending on the database you can write, for example, a trigger before insert , or write a stored procedure for inserting the data according to its own rules and not giving direct write access to the tables (although neither of the two methods is convenient, if necessary gives to do).

  • Note that it is also possible to have multiple inheritance with strategies 1 and 2 (just for example, you have discriminating "flags" instead of a single column of value).

Browser other questions tagged database oop modeling pattern-design inheritance

You are not signed in. Login or sign up in order to post.