database design - Algo así como la herencia en el diseño de la base de datos

database-design polymorphic-associations

Question

Supongamos que está configurando una base de datos para almacenar datos de prueba de choque de varios vehículos. Desea almacenar datos de pruebas de choque para lanchas rápidas, automóviles y go-karts.

Puede crear tres tablas separadas: SpeedboatTests, CarTests y GokartTests. Pero muchas de sus columnas van a ser las mismas en cada tabla (por ejemplo, la identificación del empleado de la persona que realizó la prueba, la dirección de la colisión (frontal, lateral, posterior), etc.). Sin embargo, muchas columnas serán diferentes, por lo que no desea simplemente poner todos los datos de prueba en una sola tabla, ya que tendrá bastantes columnas que siempre serán nulas para las lanchas rápidas, algunas de las cuales siempre ser nulo para los autos, y bastantes que siempre serán nulos para go-karts.

Supongamos que también desea almacenar cierta información que no está directamente relacionada con las pruebas (como la identificación del empleado del diseñador de la cosa que se prueba). Estas columnas no parecen adecuadas para poner en una tabla de "Pruebas", especialmente porque se repetirán para todas las pruebas en el mismo vehículo.

Permítanme ilustrar una posible disposición de tablas, para que puedan ver las preguntas involucradas.

Speedboats id | col_about_speedboats_but_not_tests1 | col_about_speedboats_but_not_tests2 Cars id | col_about_cars_but_not_tests1 | col_about_cars_but_not_tests2 Gokarts id | col_about_gokarts_but_not_tests1 | col_about_gokarts_but_not_tests2 Tests id | type | id_in_type | col_about_all_tests1 | col_about_all_tests2 (id_in_type will refer to the id column of one of the next three tables, depending on the value of type) SpeedboatTests id | speedboat_id | col_about_speedboat_tests1 | col_about_speedboat_tests2 CarTests id | car_id | col_about_car_tests1 | col_about_car_tests2 GokartTests id | gokart_id | col_about_gokart_tests1 | col_about_gokart_tests2

¿Qué es bueno / malo sobre esta estructura y cuál sería la forma preferida de implementar algo como esto?

¿Qué sucede si también hay información que se aplica a todos los vehículos que preferiría tener en una tabla de vehículos? La mesa CarTests se vería algo así como ...

id | vehicle_id | ... With a Vehicles table like this: id | type | id_in_type (with id_in_type pointing to the id of either a speedboat, car, or go-kart)

Esto parece ser un desastre real. ¿Cómo DEBERÍA estar configurado algo así?

Answer 1

Lo dividiría en diferentes tablas, por ejemplo, vehículo (ID, tipo, etc.) VehicleAttributes () VehicleID, AttributeID, Value), CrashTestInfo (VehicleID, CrashtestID, Date, etc.) CrashtestAttributes (CrashTestID, AttributeID, Value)

O en lugar de atributos, tablas separadas para cada conjunto de detalles similares que deberían registrarse.

Answer 2

Para mapear jerarquías de herencia en tablas de bases de datos, creo que Martin Fowler presenta las alternativas bastante bien en su libro Patterns of Enterprise Application Architecture.

http://martinfowler.com/eaaCatalog/singleTableInheritance.html

http://martinfowler.com/eaaCatalog/classTableInheritance.html

http://martinfowler.com/eaaCatalog/concreteTableInheritance.html

Si el número de campos / columnas adicionales es pequeño para las subclases, entonces la herencia de tablas únicas suele ser la más simple de tratar.

Si está utilizando PostgreSQL para su base de datos y está dispuesto a vincularse con una característica específica de la base de datos, admite la herencia de tablas directamente:

http://www.postgresql.org/docs/8.3/static/ddl-inherit.html

Answer 3

Realiza una búsqueda en Google sobre "modelado relacional gen-spec". Encontrará artículos sobre cómo configurar tablas que almacenan los atributos de la entidad generalizada (lo que los programadores de OO podrían llamar la superclase), tablas separadas para cada una de las entidades especializadas (subclases) y cómo usar claves externas para vincularlo todos juntos.

Los mejores artículos, IMO, discuten gen-spec en términos de modelado de ER. Si sabe cómo traducir un modelo de ER a un modelo relacional y de allí a tablas SQL, sabrá qué hacer una vez que le muestren cómo modelar gen-spec en ER.

Si busca en google "gen-spec", la mayor parte de lo que verá será orientado a objetos, no orientado a relaciones. Eso también puede ser útil, siempre que sepa cómo superar la desigualdad de impedancia relacional del objeto.

Answer 4

Si está utilizando SQLAlchemy , un mapeador relacional de objetos para Python, puede configurar cómo las jerarquías de herencia se asignan a las tablas de la base de datos . Los mapeadores relacionales de objetos son buenos para domesticar el tedioso SQL.

Su problema puede ser una buena opción para tablas verticales. En lugar de almacenar todo en el esquema, almacene el tipo de objeto y la clave primaria en una tabla y las tuplas de clave / valor para cada objeto en otra tabla. Si realmente estuviera almacenando pruebas de automóviles, esta configuración haría que sea mucho más fácil agregar nuevos tipos de resultados.

Answer 5

Su diseño es razonable y sigue las reglas correctas de normalización. Es posible que te falte una tabla de vehículos con un Id. Y tipo de vehículo (es decir, el "padre" para lanchas rápidas, autos y gokarts ... en el que guardarías cosas como "DesignedByUserId"). Entre la mesa del vehículo y la mesa de lanchas rápidas hay una relación de uno a uno, y entre el vehículo y la lancha rápida / automóviles / GoKarts hay una relación 1-y-1 (es decir, un vehículo solo puede tener 1 registro para lancha rápida, autos o karts) ... aunque la mayoría de los DB no ofrecen un mecanismo fácil de cumplimiento para esto.

Una regla de normalización que ayuda a identificar este tipo de cosas es que un campo debe depender únicamente de la clave principal de la tabla. En una tabla consolidada donde los resultados de las pruebas de lancha motora, autos y gokart se almacenan juntos, los campos relacionados con los automóviles dependen no solo de la fecha de la prueba sino también de la identificación del vehículo y del tipo de vehículo. La clave principal para la tabla de resultados de prueba es la fecha de prueba + identificación del vehículo, y el tipo de vehículo no es lo que hace que la fila de datos de prueba sea única (es decir, de todos modos hay una prueba el 01/01/200912: 30 pm en un vehículo específico eso es a la vez una lancha rápida y un coche ... no ... no se puede hacer).

No estoy explicando la regla de normalización particularmente bien ... pero las reglas de las formas normales 3ra / 4ta / 5a siempre me confunden cuando leo las descripciones formales. Uno de esos (3º / 4º / 5º) trata de campos que dependen de la clave principal y solo de la clave principal. La regla supone que la clave primaria ha sido identificada correctamente (la definición incorrecta de la clave primaria es demasiado fácil de hacer).

Answer 6

El type y el diseño id_in_type se llaman Asociaciones polimórficas . Este diseño rompe las reglas de normalización de múltiples maneras. Si nada más, debería ser una bandera roja que no puede declarar una restricción de clave externa real, porque el id_in_type puede hacer referencia a cualquiera de varias tablas.

Aquí hay una mejor manera de definir sus tablas:

• Haga una tabla abstracta Vehicles para proporcionar un punto de referencia abstracto para todos los subtipos de vehículos y pruebas de vehículos.
• Cada subtipo de vehículo tiene una clave principal que no se incrementa automáticamente, sino que hace referencia a Vehicles .
• Cada subtipo de prueba tiene una clave principal que no se autoincrementa, sino que hace referencia a las Tests .
• Cada subtipo de prueba también tiene una clave externa para el subtipo de vehículo correspondiente.

Aquí está el ejemplo de DDL:

CREATE TABLE Vehicles (
 vehicle_id INT AUTO_INCREMENT PRIMARY KEY
);

CREATE TABLE Speedboats (
 vehicle_id INT PRIMARY KEY,
 col_about_speedboats_but_not_tests1 INT,
 col_about_speedboats_but_not_tests2 INT,
 FOREIGN KEY(vehicle_id) REFERENCES Vehicles(vehicle_id)
);

CREATE TABLE Cars (
 vehicle_id INT PRIMARY KEY,
 col_about_cars_but_not_tests1 INT,
 col_about_cars_but_not_tests2 INT,
 FOREIGN KEY(vehicle_id) REFERENCES Vehicles(vehicle_id)
);

CREATE TABLE Gokarts (
 vehicle_id INT PRIMARY KEY,
 col_about_gokarts_but_not_tests1 INT,
 col_about_gokarts_but_not_tests2 INT,
 FOREIGN KEY(vehicle_id) REFERENCES Vehicles(vehicle_id)
);

CREATE TABLE Tests (
 test_id INT AUTO_INCREMENT PRIMARY KEY,
 col_about_all_tests1 INT,
 col_about_all_tests2 INT
);

CREATE TABLE SpeedboatTests (
 test_id INT PRIMARY KEY,
 vehicle_id INT NOT NULL,
 col_about_speedboat_tests1 INT,
 col_about_speedboat_tests2 INT,
 FOREIGN KEY(test_id) REFERENCES Tests(test_id),
 FOREIGN KEY(vehicle_id) REFERENCES Speedboats(vehicle_id)
);

CREATE TABLE CarTests (
 test_id INT PRIMARY KEY,
 vehicle_id INT NOT NULL,
 col_about_car_tests1 INT,
 col_about_car_tests2 INT,
 FOREIGN KEY(test_id) REFERENCES Tests(test_id),
 FOREIGN KEY(vehicle_id) REFERENCES Cars(vehicle_id)
);

CREATE TABLE GokartTests (
 test_id INT PRIMARY KEY,
 vehicle_id INT NOT NULL,
 col_about_gokart_tests1 INT,
 col_about_gokart_tests2 INT,
 FOREIGN KEY(test_id) REFERENCES Tests(test_id),
 FOREIGN KEY(vehicle_id) REFERENCES Gokarts(vehicle_id)
);

Alternativamente, puede declarar Tests.vehicle_id que hace referencia a Vehicles.vehicle_id y deshacerse de las claves foráneas vehicle_id en cada tabla de subtipo de prueba, pero eso permitiría anomalías, como una prueba de lancha rápida que hace referencia a la id de un gokart.