Entendiendo el diseño de tablas

1 comentario

En Firebird todos los datos que los usuarios quieren guardar se guardan (archivan o almacenan o registran, son sinónimos) en las tablas que el programador diseñó.

Es por lo tanto importante que esas tablas estén bien diseñadas ya que si no lo están varios problemas podrían ocurrir, entre ellos:

  • Desperdicio de espacio en disco
  • Consultas lentas
  • Datos incorrectos o inconsistentes

El diseño correcto de las tablas se logra mediante la normalización, y ese no es el objeto de este artículo ya que mucho se ha escrito sobre normalización y en Internet encontrarás un montón de documentación al respecto, así que asumiré que entiendes esos conceptos. Este artículo es más bien sobre el diseño de tablas con Firebird.

En una Base de Datos relacional, como las son todas las de Firebird, los datos que los usuarios almacenan se guardan en tablas. Estas tablas constan de filas y columnas. Las columnas son diseñadas por el programador y el usuario es el encargado de guardar datos en ellas.

Todas las tablas deberían tener una Primary Key, la única excepción serían las tablas que tienen solamente una fila (que se usa por ejemplo para guardar los parámetros de configuración de la aplicación).

Esa Primary Key sirve para dos objetivos:

  1. Para identificar a cada fila sin posibilidad de equivocación
  2. Para relacionar a esta tabla con otras tablas

Además, si los datos de esta tabla dependen de los datos de otra tabla, debería existir una Foreign Key que las relacione. Por ejemplo, la venta de un producto debe estar relacionada con la tabla de Productos. No podemos vender un kilo de manzanas si en la tabla de Productos no existe al menos una fila donde se guardan los datos de las manzanas. No podemos calificar a un alumno si en la tabla de Inscripciones no se encuentra la inscripción de ese alumno.

Cuando se definen las columnas de una tabla se pueden usar tipos de columnas estándar o dominios. Lo correcto es que siempre se usen dominios porque le dan al programador mucho más control sobre los datos que pueden guardarse en esa columna.

Las columnas por las cuales frecuentemente se hacen búsquedas o consultas deberían estar indexadas porque eso aumenta muchísimo la velocidad.

Pero si una columna tiene muchos valores repetidos no debería ser indexada porque eso hará que las consultas y las búsquedas sean lentísimas. Por ejemplo tener un índice según la columna sexo del alumno sería un gran error, porque esa columna solamente puede tener dos valores posibles (femenino o masculino).

Nunca debería permitirse que entre basura en una tabla. Se llama basura a cualquier  dato que está en la tabla pero que no debería estar allí. Para evitar que entre basura se usan los triggers. Ejemplos de basura pueden ser: el precio de venta es negativo, la fecha de la cobranza es anterior a la fecha de la venta, se vendió un producto inexistente, se registró la venta de un producto pero no se sabe en que fecha fue la venta ni a quien se le vendió.

Resumiendo:

  • Las tablas deben estar normalizadas
  • Todas las tablas deben tener una Primary Key (salvo que nunca puedan tener más de una fila)
  • Si los datos de una tabla dependen de los datos de otra tabla, ambas tablas deben estar relacionadas mediante una Foreign Key
  • Los índices aumentan mucho la velocidad de consulta y de búsqueda cuando son creados según las columnas correctas
  • Crear un índice según una columna que tiene muchos valores repetidos es un gran error
  • Es muchísimo más preferible usar dominios que tipos de datos estándar para definir a las columnas de una tabla porque los dominios le dan mucho mayor control al programador
  • Nunca debe permitirse que entre basura en una tabla y para evitar que entre basura se pueden usar los triggers

 

Entendiendo a las Primary Keys

15 comentarios

Las Primary Keys (claves primarias) son una parte importantísima a considerar cada vez que se diseña una Base de Datos o una tabla dentro de una Base de Datos.

¿Por qué?

Porque toda fila de una tabla necesitará, más temprano o más tarde, ser identificada exactamente, sin lugar a dudas. Y esa es justamente la utilidad de la Primary Key, para eso sirve.

Una Primary Key:

  1. No puede tener valores repetidos
  2. No puede tener valores nulos

Si tuviera valores repetidos evidentemente que no servirá para identificar a una fila exactamente, ya que habría dos o más filas con el mismo identificador. Y si pudiera tener valores nulos ocurriría el mismo problema, que dos o más filas tengan valores nulos ¿y cómo identificar a una sola de ellas en ese caso? sería imposible. Por lo tanto, las Primary Keys no pueden tener ni valores repetidos ni valores nulos.

Cuando estableces una restricción de Primary Key el Firebird automáticamente crea un índice para esa restricción ¿Por qué? porque así puede muy rápidamente descubrir si el valor que le asignas a la columna establecida como Primary Key está repetido. Y si está repetido por supuesto que lo rechazará, ya que las Primary Keys no pueden tener valores repetidos.

Una Primary Key puede estar compuesta por una columna o por más de una columna lo único realmente importante a recordar es que no puede haber ni valores repetidos ni valores nulos en esa columna (o en esas columnas, si son más de una).

Un error relativamente frecuente entre los principiantes es declarar como Primary Key a una columna que sirve para otra cosa también, no se la usa solamente como Primary Key sino para algo más también.

Por ejemplo, el número del documento de identidad, el número de la Factura de venta, el número de la cuenta contable. Aunque ninguno de estos números estén repetidos no deberían usarse como Primary Key. Pueden ser usados porque no están repetidos ni son nulos, pero no deberían usarse. ¿Y por qué no deberían usarse? porque sirven para algo más. Por ejemplo el número del documento de identidad sirve para identificar a las personas y puede aparecer en muchos informes. Y esa no es la función de una Primary Key.

En general, lo mejor es que sea el propio Firebird quien le asigne sus valores a la Primary Key ya que de esa manera te asegurarás que no existan ni valores repetidos ni valores nulos. ¿Y cómo consigues eso? Mediante un un generador (también llamado secuencia) y un trigger.

Los programas administradores gráficos del Firebird (como Flame Robin, SQL Manager, etc.) hacen eso automáticamente cuando creas una Primary Key pero si no dispones de uno de esos programas o si quieres saber lo que ocurre aquí está la explicación:

  • Se crea un generador para usarlo como Primary Key. Un generador es un número (también llamado secuencia) cuyos valores son asignados por el Firebird en forma automática, no los asigna ni el programador ni el usuario sino que lo hace el propio Firebird
    • Ese generador (como todos los generadores) es mantenido afuera de todas las transacciones de los usuarios. O sea que una vez que se le asignó un valor a un generador ese valor permanece y no es cambiado aunque la transacción sea desechada
    • El programador puede cambiar el valor de un generador pero eso no es recomendable hacer porque puede causar graves daños. Lo mejor y recomendado es dejarlo al Firebird que se encargue de asignarle valores al generador
  • Se crea un trigger que se activa antes de insertarle datos a la tabla. En ese trigger se usa la función GEN_ID(). Lo que hace esa función es sumar algebraicamente a su primer argumento el número que se encuentre en su segundo argumento. Por ejemplo:
    • GEN_ID(MiNumero, 1)                   — Le sumará 1 a la variable MiNumero
    • GEN_ID(SegundoNumero, 2)      — Le sumará 2 a la variable SegundoNumero
    • GEN_ID(OtroNumero, -5)             — Le restará 5 a la variable OtroNumero
    • GEN_ID(NumeroActual, 0)          — Le sumará 0 a la variable NumeroActual
  • El valor que se le asignó al generador es asignado a la columna que se declaró como Primary Key.

Aquí está un ejemplo:

SET TERM ^ ;

CREATE TRIGGER BI_VENTAS_VEN_IDENTI FOR VENTAS ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
   IF (NEW.VEN_IDENTI IS NULL OR NEW.VEN_IDENTI = 0) THEN
      NEW.VEN_IDENTI = GEN_ID(VENTAS_VEN_IDENTI_GEN, 1);
END^

SET TERM ; ^

Fíjate en lo que está entre el BEGIN y el END^ por el resto del código no te preocupes todavía. En este caso VEN_IDENTI es la Primary Key. Lo que se le está diciendo al Firebird es lo siguiente: “si el valor que tiene VEN_IDENTI antes de la inserción en la tabla es nulo o si el valor que tiene VEN_IDENTI antes de la inserción en la tabla es cero entonces asígnale a VEN_IDENTI el valor que obtendrás cuando al generador VENTAS_VEN_IDENTI_GEN le sumes uno”

Así que si por ejemplo VENTAS_VEN_IDENTI_GEN tenía como valor 5, después de GEN_ID() tendrá 6 y el número 6 será el asignado a la columna VEN_IDENTI.

Y por lo tanto, (si es que el Firebird no encuentra algún error antes de la inserción) el valor que tendrá la columna VEN_IDENTI cuando termine la inserción será 6.

Por supuesto que los nombres de tus generadores pueden ser más cortos, no es necesario que sean tan largos como en este ejemplo.

Resumiendo:

  • Las Primary Keys cumplen un papel importantísimo en el diseño de las tablas y de las bases de datos
  • Sirven para identificar, sin equivocación posible, a cada fila de una tabla
  • No pueden tener valores repetidos
  • No pueden tener valores nulos
  • El Firebird automáticamente le crea un índice a cada Primary Key y usa ese índice para descubrir rápidamente si se quiere insertar un valor que ya existe
  • Una Primary Key puede estar compuesta por una sola columna o por varias columnas
  • No se debería usar como Primary Key a una columna que tiene otra utilidad, que sirve para algo más (como el Documento de Identidad o el Número de la Factura). La Primary Key debe servir para Primary Key y para nada más
  • Lo mejor es que sea el propio Firebird quien se encargue de asignarle valores a las Primary Keys porque eso le ahorra trabajo al programador y además le asegura que no tendrá ni valores repetidos ni valores nulos
  • Las Primary Keys pueden ser creadas automáticamete por programas de administración gráfica como Flame Robin y SQL Manager. Lo que hacen esos programas es:
    • Crear un generador
    • Crear un trigger
    • Dentro de ese trigger asignarle un valor al generador y luego el valor del generador asignarlo a la columna que se definió como Primary Key

Entendiendo a los metadatos del programador

1 comentario

Como ya hemos visto en un post anterior:

https://firebird21.wordpress.com/2013/03/15/entendiendo-a-las-bases-de-datos/

hay dos clases de metadatos:

  • Metadatos del Firebird (los cuales siempre tienen el prefijo RDB$ o el prefijo MON$)
  • Metadatos del programador

En este artículo escribiré sobre los metadatos del programador porque los metadatos del Firebird son un asunto más avanzado y este artículo es para principiantes.

Todo lo que el programador hace dentro de una Base de Datos afecta a los metadatos de ella. El programador tiene la posibilidad de crear, modificar, o borrar metadatos. Los usuarios comunes no tienen esa posibilidad (o al menos no deberían tenerla porque sería un grave problema de seguridad).

Los metadatos que el programador define en una Base de Datos son totalmente independientes a los metadatos que define en otra Base de Datos, eso significa que cada Base de Datos tiene sus propios metadatos y ni se entera de lo que ocurre en las otras bases de datos.

¿Cuáles son los metadatos que el programador puede definir?

  • Dominios
  • Tablas
  • Restricciones
  • Índices
  • Vistas
  • Stored procedures
  • UDFs
  • Triggers
  • Excepciones
  • Generadores
  • Filtros BLOB
  • Roles
  • Triggers de la Base de Datos

Los dominios son tipos de datos propios del programador que puede usar además de los tipos de datos estándar (SMALLINT, INTEGER, DATE, CHAR, VARCHAR, etc.). La ventaja de definir dominios es que se puede acotar exactamente los valores que podrán tener las columnas que se hayan declarado con ese dominio. Por ejemplo, se podría tener un dominio que no acepte números negativos, otro dominio que solamente acepte números entre 1900 y 2020, otro dominio  que solamente acepte una “T” o una “F”, otro dominio solamente acepte hasta 20 caracteres, etc.

Las tablas son los almacenes donde los usuarios guardan sus datos. Están compuestas por filas y por columnas. El programador determina cuales serán las columnas que tendrá la tabla y los usuarios le van insertando filas. Dependiendo del tipo de una columna serán los datos que se podrán registrar en esa columna. Por ejemplo, si una columna es de tipo INTEGER en esa columna solamente se podrán guardar números enteros,  no será posible guardar palabras en esa columna, es imposible. Los tipos de la columna pueden ser estándar (o sea, los que siempre tiene el Firebird y por lo tanto todas las bases de datos) o dominios (o sea, los definidos por el propio programador según su necesidad).

Las restricciones son las limitaciones que tienen algunos datos, pueden ser:

  • Primary Key (clave primaria). Debe ser única y no puede contener NULL
  • Foreign Key (clave foránea). Relaciona una o más columnas de una tabla con una o más columnas de otra tabla
  • Check (verificación). Verifica que un dato cumpla con una (o más de una) condición
  • Unique (clave única). Debe ser única y no puede contener NULL

Cuando se crea una restricción el Firebird automáticamente crea también un índice y lo asocia con esa restricción.

Los índices son tablas auxiliares que el Firebird utiliza para:

    1. Mostrar a los datos ordenados según lo especificado en el índice
    2. Buscar un dato dentro de una tabla.

Por ejemplo, se podría usar un índice para mostrar los nombres de todos los productos, ordenados alfabeticamente. Y otro índice para buscar a la Factura número 12345.

Las vistas son consultas que se realizan frecuentemente, por eso se las guarda dentro de la Base de Datos ya que así las consultas serán mostradas más rapidamente.

Los stored procedures (procedimientos almacenados) son las instrucciones que el programador escribe para procesar los datos que los usuarios guardaron en las tablas. Los hay de dos tipos: ejecutables y seleccionables. Los ejecutables son equivalentes a los procedimientos, rutinas o funciones que tienen los lenguajes de programación. Los seleccionables son equivalentes a una tabla.

Las UDFs (User Defined Function = función definida por el usuario) son las funciones externas al Firebird y que se usan dentro de las bases de datos. Por ejemplo, podrías escribir una función en el lenguaje C que te halle la raíz cuadrada de un número. Esa función podrás luego incluirla dentro de tu Base de Datos y pasará a formar parte de ella, podrás llamarla a esa función cuando lo desees, todas las veces que quieras. Esta característica del Firebird lo hace muy poderoso porque permite que le agregues cientos o miles de funciones que hagan prácticamente cualquier cosa que necesites.

Los triggers (desencadenantes) son similares a los stored procedures en que contienen instrucciones para el procesamiento de los datos pero se diferencian en algo fundamental: los triggers son llamados automáticamente por el Firebird cada vez que los usuarios insertan o borran o modifican una fila. Son muy útiles para validar que los datos que se quieren guardar en una tabla sean todos datos válidos. Por ejemplo, podrías usar un trigger para evitar que se haga la cobranza antes de hacerse la venta. O para evitar que el precio de venta sea menor que el precio de costo, etc.

Las excepciones son los mensajes que el Servidor le envía al Cliente cuando detecta algún error. Por ejemplo podrías tener una excepción llamada E_FALTA_FECHA_VENTA que diga algo como “No puedo grabar esta venta porque falta la fecha de la venta”. Otra excepción podría llamarse E_FALTA_MONTO_VENTA y decir algo como “No puedo grabar esta venta porque falta el monto vendido”.

Los generadores, también llamados secuencias, son números cuyos valores los asigna automáticamente el Firebird, no los asigna ni el programador ni el usuario sino el Firebird. ¿Para qué se usan? para identificar a una fila y asegurarse que ninguna otra fila tenga ese mismo número. Por ejemplo, si una Primary Key es obtenida mediante un generador te asegurarás que: a) no tenga números repetidos y que b) no tenga valores nulos.

Los filtros BLOB son filtros para las columnas de tipo BLOB. ¿Y qué son las columnas de tipo BLOB? La sigla BLOB significa (Binary Large OBject = objeto binario de gran tamaño) y se la utiliza para  guardar archivos dentro de la Base de Datos, por ejemplo: fotografías, canciones, vídeos, etc. Por el momento no te preocupes por los filtros BLOB porque no son para principiantes, son algo bastante más avanzado.

Los roles son los derechos que se le asignan a un grupo de usuarios. La ventaja de usar roles es que con ellos se les puede asignar o revocar de una sola vez los derechos a todos los usuarios que pertenecen a ese rol. Por ejemplo, supongamos que Alicia, Graciela, Mirta, Raquel y Susana pertenecen al rol R_CONTABILIDAD y queremos que todas ellas tengan acceso a la vista V_BALANCE_GENERAL. En este caso al rol R_CONTABILIDAD le asignamos el derecho de acceso a V_BALANCE_GENERAL y ya está. Si no usáramos rol entonces tendríamos que hacer la misma asignación 5 veces, una vez para cada una de esas personas.

Los triggers de la Base de Datos son las instrucciones que el programador escribe para controlar cuando:

  • Alguien se conectó a la Base de Datos
  • Alguien se desconectó de la Base de Datos
  • Empezó una transacción
  • Una transacción terminó con un COMMIT
  • Una transacción terminó con un ROLLBACK

Resumiendo:

  • Todas las operaciones que el programador efectúa dentro de una Base de Datos modifican a los metadatos
  • El programador puede crear, borrar o modificar metadatos
  • Los metadatos que escribe un programador en una Base de Datos son totalmente independientes a los metadatos de todas las otras Bases de Datos
  • Los metadatos que los programadores pueden crear, borrar o modificar son los siguientes:
    • Dominios
    • Tablas
    • Restricciones
    • Índices
    • Vistas
    • Stored procedures
    • UDFs
    • Triggers
    • Excepciones
    • Generadores
    • Filtros BLOB
    • Roles
    • Triggers de la Base de Datos

Entendiendo a las Bases de Datos

5 comentarios

Si vas a usar Firebird entonces es seguro que vas a trabajar con Bases de Datos, sí o sí, por lo tanto es importante que sepas que es una Base de Datos para el Firebird.

Desde muy antiguo la gente ha archivado (guardado, almacenado, son sinónimos) datos que eran de interés para ellos.  Antes de que existieran las computadoras lo normal era archivar esos datos en papeles. Por ejemplo los comerciantes tenían un cuaderno donde anotaban todo lo que vendieron, todo lo que cobraron y cuanto era el dinero que otras personas les estaban adeudando. Ese era su archivo.

Cuando se empezaron a usar las computadoras se hacía eso mismo, lo que cambió es el lugar donde se anotaban los datos pero en esencia era la misma cosa: se registraba un dato y luego se lo archivaba para más tarde tenerlo disponible cuando se lo necesitara.

Una Base de Datos del Firebird hace mucho más que eso: no solamente archiva datos sino que también los procesa. Utilizar las Bases de Datos solamente para archivar datos es un desperdicio inmenso porque no se aprovecha toda la potencia que tiene este motor SQL.

Y ese gran poder de procesamiento que tiene el Firebird es el que lo hace tan poderoso. Por ejemplo podrías pedirle que te muestre:

  • Todas las ventas que hicimos este año, ordenadas por fechas y por números de Factura
  • El total vendido en cada día del año
  • El total vendido en lo que va del año
  • El número de la última Factura de venta
  • Si la Factura 12345 se cobró totalmente, en caso de no haberse cobrado totalmente, cuanto es el saldo que falta cobrar
  • Quien es el mejor vendedor que tenemos, cuanto vendió y cuanto se cobró de lo que vendió
  • Todo lo que se le vendió y todo lo que se le cobró a Juan Pérez
  • Cuanto debemos pagar por impuesto a las ventas este mes
  • Cual es la cantidad actual en stock que tenemos de cada producto
  • etc., etc., etc.

Los datos se archivan (guardan, almacenan) en tablas. El procesamiento de esos datos para que nos provean de información útil se hace en los stored procedures (procedimientos almacenados) y en los triggers (desencadenantes).

Cuando se crea una Base de Datos ésta nunca está vacía, el Firebird ya ha escrito dentro de esa Base de Datos tablas para su uso interno. A estas tablas que usa internamente el Firebird junto con otras cosas que veremos más adelante se las llama metadatos. También todo lo que hacen los programadores (crear nuevas tablas, crear índices, crear stored procedures, etc.) está dentro de los metadatos. En cambio lo que registran los usuarios comunes se llama datos, así, a secas.

Para diferenciar a las tablas internas de las tablas que crean los programadores, el Firebird a todas sus tablas internas y a todas las columnas dentro de esas tablas internas las prefija con los caracteres RDB$ o MON$. Por lo tanto, todas las tablas cuyos nombres empiezan con RDB$ o con MON$ son tablas internas del Firebird (o metadatos) y no deberías tocarlas o podrás destruir a la Base de Datos.

Por lo tanto dentro de los metadatos tenemos:

  • Las tablas internas del Firebird (todas las cuales empiezan con RDB$ o con MON$)
  • Lo que crearon los programadores (dominios, tablas, índices, stored procedures, triggers, etc.)

El Firebird es extremadamente quisquilloso y jamás te permitirá que hagas algo que no está permitido. Por ejemplo si quieres poner que el precio de venta de un producto es “mil doscientos veinte” no podrás hacerlo, tendrás que escribir 1.220 en números, no en letras. Como es tan estricto al principio puede resultar muy molesto, ya que no te permite que “metas la pata” aunque quieras meterla. Pero luego de un tiempo le agradeces de que sea así porque gracias a ello tus aplicaciones son muy seguras y muy confiables, nunca tienen errores que pudieron haberse evitado.

El Firebird es además rapidísimo, puede procesar millones y millones de datos en cuestión de segundos. Por supuesto que hay que saber como pedirle que lo haga.

Aunque podrías usar una Base de Datos solamente para archivar datos en ella y realizar todo el procesamiento con algún lenguaje de programación (Visual FoxPro, Visual Basic, Delphi, Java, PHP, Python, etc.) eso no es lo recomendable ¿por qué no?

  1. El procesamiento es más rápido si se hace dentro de la Base de Datos
  2. El Servidor te asegurará que todas las instrucciones sean correctas antes de que sean ejecutadas
  3. Tus stored procedures estarán disponibles para todos los lenguajes de programación que puedan conectarse a tu Base de Datos
  4. Si cambias la estructura de una tabla y la nueva estructura es incompatible con lo escrito dentro de un stored procedure lo sabrás al instante y podrás corregir el problema enseguida
  5. Si necesitas cambiar un stored procedure, al hacerlo ya estará disponible para todos los programas (escritos en el lenguaje que sea) que llaman a ese stored procedure

Resumiendo:

  • Una Base de Datos se usa para archivar (guardar, almacenar) datos y también para procesar dichos datos
  • Cuando se crea una nueva Base de Datos ésta nunca está vacía
  • Todas las Bases de Datos tienen metadatos dentro de ella que pueden ser:
    • Creados por el Firebird (y en ese caso tienen los prefijos RDB$ o MON$)
    • Creados por los programadores
  • Lo que hacen los usuarios comunes es archivar datos en las tablas que crearon los programadores
  • Para procesar los datos que ingresaron los usuarios comunes se escriben instrucciones dentro de los stored procedures y dentro de los triggers. Se podría obviar los stored procedures y los triggers y realizar todo el procesamiento mediante las instrucciones de un lenguaje de programación, pero eso sería un error porque se estaría desaprovechando gran parte de la potencia del Firebird
  • El Firebird es muy estricto y jamás te permite que hagas algo incorrecto. Esto puede resultar molesto al principio pero luego cuando te acostumbras se lo agradeces infinitamente
  • Es también rapidísimo, pudiendo procesar millones y millones de datos por segundo