9.2: Apéndice B - Trabajo con mesas múltiples

Introducción

El historiador y diseñador de bases de datos Mark Merry observa:

La mayoría de las bases de datos consisten en datos contenidos en más de una tabla, y esto es especialmente cierto para bases de datos donde los datos se derivan de fuentes históricas. Se crean relaciones entre las tablas para conectar los datos de una a los datos de la otra: más precisamente, las relaciones se utilizan para conectar registros específicos de una tabla a registros específicos en otra. En muchos sentidos, las relaciones, y todo el modelo de datos relacionales, comprenden el aspecto más difícil del diseño de una base de datos, y no necesariamente porque son difíciles de crear realmente. Lo difícil de las relaciones es por qué las necesitamos; las razones para usar datos relacionados pueden parecer oscuras e innecesarias al inicio de un proyecto de base de datos, especialmente si tienes poca experiencia en el uso de bases de datos. Sin embargo, son sumamente importantes. En esencia lo que las relaciones nos permiten hacer es doble: en primer lugar nos permiten simplificar de manera muy significativa el proceso de entrada de datos (y de paso al mismo tiempo nos permiten proteger la calidad de los datos que ingresamos limitando los errores de entrada de datos); y en segundo lugar sirven para asegurar que los resultados de nuestras consultas son precisas al dejar claro precisamente qué es lo que se está consultando.

Funciones de las relaciones

Estas funciones duales de las relaciones se ilustran mejor con un ejemplo.

Imagine una base de datos que contenga datos sobre las personas y los autos de su propiedad, que comprenda información personal sobre nombre, género, fecha de nacimiento, etc., así como información sobre el tipo de automóvil y el color del automóvil. Hay dos formas en que esta base de datos podría diseñarse:

Un modelo de datos de tabla única (archivo plano), donde se ingresó en la misma tabla toda la información sobre personas y automóviles

Un modelo de datos relacionales donde se crean dos tablas, una para contener información sobre personas y otra para contener información sobre automóviles

Los dos escenarios son factibles y te permitirán realizar un análisis detallado de las personas y sus sombreros, pero cada uno trae consigo algunas consecuencias muy significativas si se elige.

Escenario A: toda la información en una tabla

En este escenario tienes una tabla con varios campos para capturar toda la información relacionada con personas y autos disponible de tus fuentes: Los principios de diseño de bases de datos dicen que no debes combinar diferentes entidades en una sola tabla (en este caso 'personas' y 'autos' siendo las entidades), ya que esta confunde el 'significado' subyacente de sus datos. La buena práctica diría: los dueños de autos son una entidad, los autos son otra, así que crea diferentes mesas para ellos. No obstante, es posible combinar entidades en tablas únicas, y para nuestros fines aquí es útil ver las consecuencias de hacerlo.

PersonID

FirstName

Apellidos

DoB

DoD

Carrera

Ubicación

Género

CariD

CarType

CarMake

CarModel

CarColor

Ingresar un número de registros en esta tabla daría como resultado datos que se asemejan a:

PersonID

FirstName

Apellidos

DoB

DoD

Carrera

Ubicación

Género

CariD

CarType

CarMake

CarModel

CarColor

1

Anthony

Soprano

22/1/1958

15/11/2003

Blanco

Nuevo Jersy

Macho

1

Sedán

Honda

Accord

Negro

2

John

Nieve

15/02/1998

Blanco

Invernalia

Macho

2

Coupé

Honda

Cívico

Rojo

3

Scarlett

O'Hara

16/5/1842

3/10/1931

Blanco

Tara

Hembra

3

SUV

Nissan

Rogue

Blanco

4

Sherlock

Holmes

29/6/1859

4/4/1940

Blanco

Londres

Macho

4

SUV

Cadillac

Explanada

Plata

5

George

Jefferson

1/1/1956

20/12/2004

Negro

Nueva York

Macho

4

SUV

Cadillac

Explanada

Plata

6

Louise

Jefferson

3/3/1960

10/8/2010

Negro

Nueva York

Hembra

5

SUV

Ford

Escape

Azul

7

Laverne

Cox

7/5/1981

Negro

Nueva Jersey

6

Convertible

Volkswagen

Golf

Oro

8

Lucille

Ricardo

19/09/1930

1/5/2004

Blanco

Nueva York

Hembra

7

Sedán

Pontiac

Bonneville

Aqua

9

Ricky

Ricardo

10/1/1926

20/1/1989

Hispano

Cuba

Macho

7

Sedán

Pontiac

Bonneville

Aqua

10

Fred

Mertz

11/1/1905

1/2/1975

Blanco

NUEVA YORK

Macho

11

Ethel

Mertz

11/5/1912

30/6/1990

Blanco

Ciudad de Nueva York

Hembra

Con una mesa como esta podríamos realizar algunos análisis sofisticados sobre tipos de personas, tipos de autos, marca, modelo, materiales utilizados, la correlación entre género, edad y tipo de automóvil, distribución por género de los colores del automóvil y así sucesivamente, lo que obviamente sería de enorme beneficio para los historiadores interesados en esto tipo de investigación.

A medida que continuamos examinando nuestras fuentes, encontramos que algunas personas poseen más de un automóvil. Algunos autos son propiedad de más de una persona. Esta es una relación uno a muchos: una persona puede tener más de un automóvil. Entonces, en este escenario de diseño de base de datos podríamos encontrar registros que aparecen en la tabla de la siguiente manera:

PersonID

FirstName

Apellidos

DoB

DoD

Carrera

Ubicación

Género

CariD

CarType

CarMake

CarModel

CarColor

1

Anthony

Soprano

22/1/1958

15/11/2003

Blanco

Nuevo Jersy

Macho

1

Sedán

Honda

Accord

Negro

2

John

Nieve

15/02/1998

Blanco

Invernalia

Macho

2

Coupé

Honda

Cívico

Rojo

3

Scarlett

O'Hara

16/5/1842

3/10/1931

Blanco

Tara

Hembra

3

SUV

Nissan

Rogue

Blanco

4

Sherlock

Holmes

29/6/1859

4/4/1940

Blanco

Londres

Macho

4

SUV

Cadillac

Explanada

Plata

5

George

Jefferson

1/1/1956

20/12/2004

Negro

Nueva York

Macho

4

SUV

Cadillac

Explanada

Plata

6

Louise

Jefferson

3/3/1960

10/8/2010

Negro

Nueva York

Hembra

5

SUV

Ford

Escape

Azul

7

Laverne

Cox

7/5/1981

Negro

Nueva Jersey

6

Convertible

Volkswagen

Golf

Oro

8

Lucille

Ricardo

19/09/1930

1/5/2004

Blanco

Nueva York

Hembra

7

Sedán

Pontiac

Bonneville

Aqua

9

Ricky

Ricardo

10/1/1926

20/1/1989

Hispano

Cuba

Macho

7

Sedán

Pontiac

Bonneville

Aqua

10

Fred

Mertz

11/1/1905

1/2/1975

Blanco

NUEVA YORK

Macho

11

Ethel

Mertz

11/5/1912

30/6/1990

Blanco

Ciudad de Nueva York

Hembra

12

Laverne

Cox

7/5/1981

Negro

Nueva Jersey

8

Sedán

Ford

Tauro

Verde

Como puedes ver la tabla ahora tiene 12 registros en lugar de 11, y hemos ingresado a Lavern Cox dos veces (lo que creó un nuevo problema ya que hay dos personID's separados asociados con ella). La razón por la que hemos ingresado a esto dos veces es porque Lavern Cox posee dos autos, como puedes ver en los campos relacionados con los autos. Algunos individuos poseen el mismo cuidado. Como resultado, algunos de nuestros campos tienen valores duplicados en ellos, y esto es a la vez un problema, y una pista de que esta tabla podría estar mejor diseñada como parte de un modelo de datos relacionales. Se debe evitar duplicar la información en los registros de esta manera por varias razones. En primer lugar, la entrada de datos consume bastante tiempo sin tener que ingresar la misma información en más de una ocasión. En segundo lugar, cuantas más veces ingrese la misma información en la base de datos, más alcance habrá para ingresar algo incorrectamente, como lo hemos hecho aquí con el valor de ubicación de Anthony Soprano.

Este error en particular podría haberse evitado mediante el uso de una variedad de herramientas dentro de la base de datos que están diseñadas para mitigar los errores de entrada de datos.

Pero el problema más grave que plantea esta duplicación de información es un tercer problema —que es que esto afectará negativamente a algunos tipos de análisis al proporcionar resultados falsos a las consultas. Podríamos escribir una consulta para responder a la pregunta: '¿cuántos individuos poseen un automóvil?' La consulta cuenta el número de registros de personas que poseían autos esta sería la respuesta a la pregunta. La consulta indicaría que hubo 10 personas que eran dueños de autos. Pero, de hecho, sólo hay nueve individuos separados que son dueños de un automóvil. Laverne se cuenta dos veces por poseer dos autos cuando solo debería haber sido contada una vez.

Escenario B: dos tablas relacionadas

Entonces, ¿qué sucede si modelamos la información de la persona y del automóvil de acuerdo con la buena práctica de tener una mesa separada para cada entidad? Acabaríamos con dos mesas:

Mesa de persona:

PersonID	FirstName	Apellidos	DoB	DoD	Carrera	Ubicación	Género
1	Anthony	Soprano	22/1/1958	15/11/2003	Blanco	Nuevo Jersy	Macho
2	John	Nieve	15/02/1998		Blanco	Invernalia	Macho
3	Scarlett	O'Hara	16/5/1842	3/10/1931	Blanco	Tara	Hembra
4	Sherlock	Holmes	29/6/1859	4/4/1940	Blanco	Londres	Macho
5	George	Jefferson	1/1/1956	20/12/2004	Negro	Nueva York	Macho
6	Louise	Jefferson	3/3/1960	10/8/2010	Negro	Nueva York	Hembra
7	Laverne	Cox	7/5/1981		Negro	Nueva Jersey
8	Lucille	Ricardo	19/09/1930	1/5/2004	Blanco	Nueva York	Hembra
9	Ricky	Ricardo	10/1/1926	20/1/1989	Hispano	Cuba	Macho
10	Fred	Mertz	11/1/1905	1/2/1975	Blanco	NUEVA YORK	Macho
11	Ethel	Mertz	11/5/1912	30/6/1990	Blanco	Ciudad de Nueva York	Hembra

Mesa de coche:

PersonID	CariD	CarType	CarMake	CarModel	CarColor
1	1	Sedán	Honda	Accord	Negro
2	2	Coupé	Honda	Cívico	Rojo
3	3	SUV	Nissan	Rogue	Blanco
4	4	SUV	Cadillac	Explanada	Plata
5	4	SUV	Cadillac	Explanada	Plata
6	5	SUV	Ford	Escape	Azul
7	6	Convertible	Volkswagen	Golf	Oro
7	8	Sedán	Ford	Tauro	Verde
8	7	Sedán	Pontiac	Bonneville	Aqua
9	7	Sedán	Pontiac	Bonneville	Aqua

Ingresar los dos conjuntos de información en las dos tablas separadas nos permite evitar todos los problemas mencionados anteriormente, y crucialmente, nos permitirá ejecutar nuestras consultas de forma segura sabiendo que cada vez se devolverá el número correcto de registros.

Las tablas se relacionan por una relación que conecta uno o más campos en una tabla con uno o más campos en la segunda tabla. En nuestras tablas Personas y Autos, el campo utilizado en la relación es el campo personID, donde se agrega el número de identificación de la persona al registro en la tabla Cats para aquellos autos pertenecientes a esa persona (así que la persona número 7, Laverne Cox, tiene su número de identificación asociado a los registros de los dos autos que posee.

Tipos de relaciones

Es importante entender que existen diferentes tipos de relación que pueden existir entre dos tablas. Estas diferencias son una función de la conexión lógica y semántica entre la información entre las dos tablas.

Los tres tipos de relaciones

Hay tres tipos de relación que pueden existir entre dos tablas en una base de datos, no todas las cuales son útiles o deseables.

Relaciones uno a uno:

Esta relación existe donde un registro en la Tabla A solo puede tener un registro relacionado en la Tabla B, y un registro en la Tabla B solo puede tener un registro coincidente único en la Tabla A

Por ejemplo, un estado solo puede tener una ciudad capital, y una ciudad capital sólo está en un estado.

Este tipo de relación es inusual en una base de datos. Por lo general, o la información se combina en una tabla, o se determina que parte de esa información no es necesaria para registrar. Lo importante a recordar con las relaciones uno a uno es que el software de base de datos que utilices para construir tu base de datos te permitirá crear este tipo de relación, y que no creará ningún problema a la hora de ejecutar consultas.

Relaciones uno a muchos:

Esta relación existe donde un registro en la Tabla A puede tener ninguno, uno o más registros coincidentes en la Tabla B, pero un registro en la Tabla B solo puede tener uno registros coincidentes en la Tabla A.

Por ejemplo, una madre puede tener más de un hijo, pero un niño solo puede tener una madre biológica

Este es el tipo de relación más común que se encuentra en las bases de datos, y suele ser el tipo que quieres incorporar a tus diseños. Como se ilustra en el escenario de personas y autos este tipo de relación se utiliza para superar los tipos de problemas que surgen dentro de la base de datos cuando la información extraída de las fuentes requeriría la duplicación de datos si se ingresa en una sola tabla.

Relaciones de muchos a muchos:

Esta relación existe donde un registro de la Tabla A no puede tener ninguno, uno o muchos registros coincidentes en la Tabla B, y un registro en la Tabla B puede tener ninguno, uno o más de uno registros coincidentes en la Tabla A

Por ejemplo, un autor puede escribir más de un libro y un libro puede ser escrito por más de un autor.

Si descubre este tipo de relación dentro del diseño de su base de datos, entonces tiene un desafío que deberá abordarse antes de poder continuar con la construcción de la base de datos. Las relaciones de muchos a muchos son difíciles de controlar y pueden romper fácilmente una consulta y devolver galimatías o nada en absoluto. Se requieren habilidades y esfuerzos considerables para manejar las relaciones de muchos a muchos. Estos deben evitarse cuando sea posible.

Desafortunadamente, con frecuencia vemos que aparecen relaciones de muchos a muchos al modelar información histórica. Tratar con una relación de muchos a muchos requiere la creación de una mesa, a veces llamada Mesa de Junction, para sentarse entre las dos tablas relacionadas. Esta Tabla de Junction actuará de manera abstracta —los datos que contendrá no serán información como tal, sino que servirán para separar la relación de muchos a muchos en dos relaciones uno a muchos.

Relación de muchos a muchos entre tablas de Autor y Libro

Tome la base de datos que contiene una tabla sobre Autores y una tabla sobre Libros, que podría diseñarse de acuerdo con el Diagrama de Relación de Entidades que se muestra arriba. Esta es una relación de muchos a muchos. Un autor puede escribir varios libros. Un libro puede tener múltiples autores. Para superar la relación de muchos a muchos, insertaríamos una tabla de unión para escupir la relación en dos relaciones uno a muchos, como se indica a continuación.

Relación de muchos a muchos entre las tablas Author y Book divididas con una tabla de unión

Tenga en cuenta que cada registro de la Tabla de cruces contiene tres campos: un ID único para cada registro (ID de cruce) y luego un campo para cada uno de los ID de autor y los ID de libro. Por lo tanto, cada registro se convierte en una combinación única de ID de autor y libro, lo que indica qué libros fueron escritos por qué autores:

Relación de muchos a muchos entre las tablas Author y Book divididas con una tabla de cruce: muestra datos

La tabla de unión aquí está efectivamente eludiendo la relación de muchos a muchos entre libros y autores, y cada registro que contiene actúa como una declaración que vincula a uno o más autores con uno o más libros. Los dos primeros registros en la Tabla de Junction, por ejemplo, indican que Author ID 1 fue el escritor de Book IDs 1 y 2, mientras que los dos últimos registros indican que Book ID 9 fue coautor por Author IDs 2 y 5. La relación entre libros y autores es manejada por la Mesa de Junction, mientras que los detalles sobre libros y autores se guardan en sus respectivas tablas.

Esta disposición permite que la base de datos ejecute consultas que se basen en la información de las tablas Libro y Autor cuando de otro modo no podría hacerlo debido a la relación de muchos a muchos. Por lo tanto, es una técnica muy valiosa a tener en cuenta a la hora de identificar las relaciones entre tablas como parte del proceso de diseño de la base de datos.

Modelado de relaciones entre entidades

Introducción

En esta sección se describen las tareas que implica realizar la traducción y conversión de información de fuentes a datos. Estos procesos se conocen colectivamente como Entity Relationship Modelling (ERM). La ERM es una actividad compleja, y que puede ser desafiante al principio. Afortunadamente, sin embargo, las etapas de la ERM se inspiran estrechamente en las habilidades y experiencias que un historiador utiliza como cuestión de rutina durante la investigación. La dificultad del proceso ERM es directamente proporcional a la complejidad de las fuentes utilizadas en la investigación, siendo algunos tipos de fuentes (relativamente) más simples de modelar que otras. Fuentes altamente estructuradas como rendimientos censales, listas de habitantes, libros de encuestas, etc. serán más fáciles de modelar que las fuentes 'semiestructuradas' como los inventarios de sucesiones, que a su vez presentarán menos problemas que el material completamente desestructurado como textos narrativos y entrevistas, etc. Sin embargo, todos tendrán sus propias características y problemas particulares para complicar el modelado.

El proceso ERM produce varios resultados. Hace que decidas qué es lo que va a lograr la base de datos en términos de funciones. Identifica los tipos de información que se pueden obtener de las fuentes. Además de solidificar el objetivo de la base de datos, esto le ayuda a decidir qué información de las fuentes se debe ingresar y cuál se puede omitir. ERM hace que se considere en detalle acerca de los componentes de la base de datos — tablas, campos, relaciones, tipos de datos, etc. Por último, ERM promueve la reflexión sobre las capas de la base de datos, como qué información necesita estar en la capa fuente y debe estar en la capa de estandarización. El proceso ERM te deja con una idea clara de cómo será la base de datos, además de proporcionar un diagrama de trabajo de la base de datos (un Diagrama de Relación de Entidades [ERD]).

Modelado de relaciones entre entidades (ERM)

Etapa 1: Determinar el propósito de la base de datos

Esta etapa es siempre el punto de partida del proceso ERM; es particularmente vital si está utilizando el enfoque orientado al método para su diseño. En este punto debes decidir qué información quieres conservar y qué quieres descartar; tendrás que estar preparado para cumplir con las consecuencias de estas elecciones a lo largo del ciclo de vida de tu proyecto de base de datos. Aunque siempre es teóricamente posible modernizar el diseño de su base de datos para incluir información que inicialmente había decidido descartar, rara vez es un asunto trivial hacerlo, particularmente si tiene que ingresar a otra fase de entrada de datos para recopilar la nueva información.

Etapa 2: Listar entidades

Cuando sepa lo que quiere que haga su base de datos, divida su información anticipada en temas discretos: cada sujeto, o entidad, evolucionará hacia una tabla separada. Separar las entidades en distintas tablas para fines de eficiencia, para evitar ambigüedades, y porque para la máxima flexibilidad en la consulta.

Esta etapa del proceso suena engañosamente simple, pero de hecho es probablemente el paso más difícil de todo el proceso. No se sorprenda si tiene que hacer algo de esto más de una vez o hacer algunas revisiones significativas.

Por ejemplo, consideremos un proyecto de investigación que investigaba elecciones políticas en Bristol del siglo XVIII, y las fuentes consistieron en una colección de libros de encuestas que registraban los votos emitidos por los electorados locales en las comunidades donde tuvieron lugar las elecciones:

Una lista exacta de los votos de los Freeholders y Freemen de la ciudad y condado de Bristol tomada en la elección de diputados al Parlamento (Bristol, 1722) p.19.

Con fuentes como estas podríamos perseguir una pregunta de investigación que era algo así como: 'Analizar los determinantes geográficos y económicos del voto a principios del ^siglo XVIII Bristol'. Con una pregunta como esta nos interesaría la geografía, la situación económica y los patrones de votación en relación a un grupo de individuos. En cuanto a las entidades, podríamos concluir que solo hay una: la gente, en realidad, más precisamente, estaríamos considerando a los votantes, lo que nos llevaría a la posición de decidir que necesitaríamos una mesa en la que ingresáramos nuestra información sobre los votantes.

Sin embargo, si estuviéramos utilizando materiales testamentarios para nuestra investigación y quisiéramos crear una base de datos de información obtenida de testamentos, deberíamos considerar las entidades de esta fuente. Podríamos concebir que nuestras entidades —nuestros sujetos discretos o cada mesa— se dividieran en 'documento', con cada testamento siendo tratado como un documento diferente; 'persona', que contiene información sobre diferentes tipos de personas y sus roles —testador, destinatario, ejecutor etc.; 'legado' con un rango de tipos de información asociada; y 'objeto', siendo el objeto de un legado. Si nuestra investigación estuviera interesada en la cultura material de un periodo o lugar, esta última entidad sería particularmente importante, mientras que si el proyecto se refería únicamente a redes de personas e interrelaciones sociales, la entidad 'objeto' podría no ser necesaria.

Quizás valga la pena considerar la inclusión de tres entidades comúnmente elegidas en el diseño de su base de datos:

1. Personas — con una entidad afín de 'rol' (siendo la razón por la que están presentes en la fuente)
2. Documento — donde se puede ingresar material de archivo y bibliográfico (y así permitir el seguimiento de cada dato en la base de datos hasta su origen)
3. Evento: una entidad un poco más abstracta, una que describe una instancia de lo que sea que sean sus registros de origen (un juicio, una evaluación fiscal, una elección, etc.) y donde se puede registrar información sobre fechas.

Etapa 3: Identificar las relaciones entre las entidades

Aquí identificas cómo están relacionadas tus entidades y qué tipo de relación existe entre ellas. Esto requiere un poco de pensamiento abstracto y requerirá algo de práctica. Es típico que esta etapa resulte en volver a visitar la etapa 2 y redefinir una o más de las entidades que eligió originalmente.

Si volviéramos a la base de datos de testamentos mencionados en la etapa 2 con las entidades 'documento', 'persona', 'legado' y 'objeto', tendríamos que desmarcar la naturaleza de las relaciones entre estas entidades. Podríamos decidir lógicamente que las relaciones se verían así (las puntas de flecha representan los “muchos” lados de una relación uno a muchos:

 

Ejemplo de relaciones identificadas entre entidades (testamentos)

Un solo documento (testamento) puede contener información sobre más de una persona y también sobre más de un legado, mientras que un legado puede incluir información sobre más de un objeto, por lo que todas estas relaciones son de uno a muchos.

Etapa 4: Investigar y rectificar problemas

Esta etapa es bastante autoexplicativa. Es posible detectar problemas con el diseño incipiente incluso en este punto relativamente temprano del proceso de ERM, y si existen es mejor hacerlo aquí que después de invertir el trabajo en las etapas posteriores.

Busque en particular:

Relaciones que no parecen ser uno a muchos: recuerde que no puede tener entidades relacionadas por una relación de muchos a muchos, y si bien puede tenerlas relacionadas a través de una relación uno a uno, puede valer la pena repensar las dos entidades involucradas.

Relaciones redundantes: si las entidades pueden vincularse de más de una manera, debe averiguar qué enlace debe mantenerse y cuál debe descartarse, si la Tabla A está relacionada con la Tabla B y la Tabla B está relacionada con la Tabla C, entonces las Tablas A y C ya están por definición relacionadas y no necesitan una relación 'directa' existir entre los dos

Etapa 5: Listar todos los atributos asociados a cada entidad e identificar claves

Esta etapa consiste en listar los atributos de cada entidad que ha sido identificada en las etapas previas del proceso de ERM, al decidir sobre los campos que deben ocurrir en cada tabla. Cada campo contiene una pieza de información sobre el tema de la entidad. Cuando haya identificado los atributos para cada entidad, entonces determina qué campo (s) actuarán como claves primarias y foráneas en cada tabla.

Ejemplo de atributos y claves identificadas dentro de entidades (testamentos)

Los términos en negro son entidad (nombres de tabla), los de rojo son claves primarias, los de verde son claves externas y los de gris son los restantes atributos/campos, algunos de los cuales pertenecen a la capa Source de la base de datos y algunos a su capa de Normalización.

Generalmente es una buena idea incluir un campo genérico 'Notas' en la mayoría de las tablas, con el tipo de datos memo. Esto es probable que resulte salvavidas en momentos de crisis al ingresar datos.

Etapa 6: Construir el Diagrama de Relación de Entidad (ERD)

Una vez que haya completado la etapa 5, estará en condiciones de crear el ERD para el diseño de su base de datos, que se parecerá al ejemplo muy simplificado anterior.