sql - una - ¿Cuáles son algunos de sus estándares de base de datos más útiles?

sistema de gestión de bases de datos relacionales (30)

El nombre de la tabla coincide con el nombre de la clave principal y la clave de descripción

Recientemente, después de años de estar de acuerdo con esto, abandoné el barco y ahora tengo una columna de "ID" en cada mesa.

Sí, lo sé, cuando se vinculan las tablas, ¡es bastante! Pero también lo es vincular ProductID a ProductID, entonces uhh, ¿por qué escribir más?

Esta:

SELECT p.Name, o.Quantity FROM Products p, Orders o WHERE o.ProductID = p.ID

Es un poco mejor que esto:

SELECT p.Name, o.Quantity FROM Products p, Orders o WHERE o.ProductID = p.ProductID

Tenga en cuenta que ambos requerirán prefijos de tabla o alias. Pero no solo estoy escribiendo un poco menos (multiplico eso en docenas de tablas con largos nombres descriptivos y se agrega rápidamente en una aplicación de uso intensivo de datos) sino que también hace que sea más fácil saber qué tabla es la tabla principal en cada combinación, lo cual Al unir 8-10 tablas en una consulta, puede ayudar bastante.

Tengo algunas ideas, algunas que he acumulado a lo largo del tiempo, pero realmente quiero saber qué hace que las cosas funcionen sin problemas cuando se modela la base de datos:

El nombre de la tabla coincide con el nombre de la clave principal y la clave de descripción
Los esquemas son por área funcional
Evite las claves primarias compuestas donde sea posible (use restricciones únicas)
Camel Case nombres de tablas y nombres de campos
No prefija tablas con tbl_, o procs con SP_ (sin notación húngara)
Las bases de datos OLTP deben ser al menos en BCNF / 4NF

No use nombres de tipo en los nombres de campo. Los chicos mayores recordarán el viejo estándar MS de lpszFieldName y la estupidez que siguió.
Use nombres de campo descriptivos que sigan las convenciones de lenguaje normales. Por ejemplo, "FirstName" en lugar de "NameFirst"
Cada palabra en el nombre del campo está en mayúscula
Sin guiones bajos
No utilice palabras clave normales como "Índice"
No prefija NADA con el tipo de objeto. Por ejemplo, NO usamos tblCustomers o spCustomersGet. Estos no permiten una buena clasificación y proporcionan un valor cero.
Use esquemas para definir áreas separadas de la base de datos. Tales como ventas. Clientes y Hr.Empleados. Esto eliminará la mayoría de los prefijos que usa la gente.
Los bucles de cualquier tipo deben verse con sospecha. Por lo general, hay una mejor manera basada en conjunto.
Use vistas para combinaciones complicadas.
Evite combinaciones complicadas cuando sea posible. Puede ser más agradable desde el punto de vista estético tener una tabla CustomerPhoneNumbers; pero, sinceramente, ¿cuántos números de teléfono realmente necesitamos almacenar? Solo agregue los campos a la tabla Clientes. Sus consultas DB serán más rápidas y mucho más fáciles de entender.
Si una tabla llama a un campo "EmployeeId", CADA UNA TABLA INDIVIDUAL que lo haga referencia debería usar ese nombre. No es necesario llamar CustomerServiceRepId solo porque está en una tabla de extensión.
Casi todas las tablas tienen la terminación "s". Por ejemplo: clientes, pedidos, etc. Después de todo, la tabla contiene muchos registros ...
Evalúe sus consultas, índices y relaciones de claves externas con una herramienta de análisis. Incluso aquellos que pueden generarse para usted. Te sorprenderías.
Las tablas de enlaces que admiten muchas relaciones muchas tienen tablas vinculadas en el nombre. Por ejemplo, SchoolsGrades. Es muy fácil decir por nombre de tabla lo que hace.
Se consistente. Si comienza un camino con sus convenciones, no cambie de caballo hasta la mitad a menos que esté dispuesto a refactorizar todo el trabajo anterior. Esto debería frenar cualquier idea de "¿no sería genial si ...?" Que termina causando confusión y grandes cantidades de reprocesos.
Piensa antes de escribir. ¿Realmente necesitas esa tabla, campo, sproc o vista? ¿Estás seguro de que no está cubierto en otro lado? Obtener consenso antes de agregarlo. Y si por alguna razón tienes que sacarlo, primero habla con tu equipo. He estado en lugares donde los administradores de bases de datos hacen cambios diarios sin tener en cuenta a los desarrolladores. Esto no es divertido

13- Evalúa tus consultas

Es verdad. A veces no obtienes lo que querías.

Para mí, siempre es útil nombrar las tablas y los campos con su contenido exacto y (para nosotros) en español claro y usar Upper Camel Case, sin espacios en blanco:

Nombre de usuario: NombreUsuario

Primer Apellido: ApellidoPaterno

Segundo apellido: ApellidoMaterno

etcétera etcétera

Además de la normalización de 3NF o BCNF (más sobre esto en esta pregunta ), he encontrado lo siguiente para ser útil:

Las tablas de nombres como nombres en plural
Nombre de las columnas como sigulares

Entonces una tabla de "Personas" tiene una columna "PersonID".

No hay nada de malo en las claves compuestas, siempre que las reglas de 3NF o BCNF aún se mantengan. En muchos casos (como el caso "muchos a muchos"), esto es completamente deseable.
Evite repetir el nombre de la tabla en los nombres de las columnas. peoplePersonID está mejor escrito como table.column de todos modos, y mucho más legible y, por lo tanto, autodocumentado. People.PersonID es mejor, al menos para mí.
ON DELETE CASCADE debe usarse con mucho cuidado .
Recuerde que NULL significa una de dos cosas: o es desconocido o no es aplicable.
Recuerde también que los valores NULL tienen efectos interesantes en las uniones, así que practique las combinaciones externas IZQUIERDA, DERECHA y FULL.

Algunos gustos y aversiones.

Mi opinión es que los prefijos son horribles en todos los aspectos. Actualmente trabajo en un sistema donde las tablas están prefijadas, y las columnas dentro de las tablas tienen el prefijo 2 acrónimos de nombre de tabla de letras, desperdicio al menos 30 minutos cada día trabajando en esta base de datos porque el acrónimo no es lógico. Si desea denotar algo con un prefijo, utilice un propietario de esquema en su lugar.

Usando NVarchar desde el inicio de un proyecto si hay incluso una ligera pista de que los datos de texto necesitarán soportar caracteres multilingües. La actualización de grandes bases de datos debido a la falta de planificación y pensamiento avanzados es un dolor y un desperdicio de tiempo.

División de cada condición dentro de una cláusula where en una nueva línea para legibilidad (en y no en las declaraciones entre corchetes y con pestañas). Creo que este es el estándar importante para mí.

Trabajé en una compañía donde un estándar era que las comas siempre deben colocarse al comienzo de una línea cuando se realizan declaraciones de parámetros o variables. Esto aparentemente lo hizo más legible sin embargo lo encontré una pesadilla completa.

Algunos otros (aunque pequeños) comentarios para tirar contra la pared ...

Los esquemas de base de datos de SQL Server pueden ser útiles tanto para organizar tablas y procedimientos almacenados como para controlar la seguridad.

Cada tabla transaccional siempre debe rastrear quién y cuándo creó el registro, así como también actualizar el registro en columnas separadas. He visto una implementación que simplemente usaba la "fecha de actualización" que puede llevar a desafíos de auditoría en el futuro.

Use GUID''s para identificadores de fila para todas las filas de proyectos con requisitos de sincronización / sin conexión.

Asegúrese de que cada elección de varchar / nvarchar sea apropiada.

Asegúrese de que cada elección de columna NULLable sea apropiada, evite columnas NULLables donde sea posible, permitiendo que NULL sea la posición justificable.

Independientemente de otras reglas que pueda utilizar en las sugerencias aquí, crearía un procedimiento almacenado en la base de datos que se puede ejecutar regularmente para determinar el estado del sistema para cualquier regla o estándar que tenga (parte de esto es un poco de SQL -Servidor específico):

Busque registros huérfanos en los casos en que la integridad referencial del sistema DBMS no pueda usarse por alguna razón (en mi sistema tengo una tabla de procesos y una tabla de pruebas, por lo que mi system_health SP busca procesos sin pruebas, ya que solo tengo un relación FK unidireccional)
Busque esquemas vacíos
Busque tablas sin claves primarias
Busque tablas sin ningún índice
Busque objetos de base de datos sin documentación (usamos las propiedades de SQL Server Extended para colocar la documentación en la base de datos; esta documentación puede ser tan detallada como la columna ).
Busque problemas específicos del sistema: tablas que deben archivarse, excepciones que no son parte del procesamiento normal mensual o diario, ciertos nombres de columna comunes con o sin valores predeterminados (CreateDate, por ejemplo).
Busque UDF no deterministas
Busque los comentarios TODO para asegurarse de que el código en el DB no tenga de alguna manera un código no probado o previo al lanzamiento.

Todo esto se puede automatizar para darle una idea general de la salud del sistema.

Buen diseño de base de datos y normalización .

Documente todo; La documentación del tipo wiki es fácil de configurar y el software es gratuito.

Asegúrese de comprender primero la interfaz y diseñar la base de datos en segundo lugar. La mayoría de las veces es mucho mejor saber cómo deben funcionar los datos que va a utilizar y luego diseñar la base de datos. La mayoría del diseño de DB malo sucede a medida que las cosas evolucionan, no por adelantado.

Luego defina el estándar de la base de datos y la versión con la que va a trabajar. Definir estándares para los elementos de código (vistas, funciones, etc.), nombres de bases de datos; nombrando convenciones para columnas, tablas; escriba convenciones para columnas; plantillas de codificación.

Dedique tiempo a considerar cómo se definen los tipos que tienen tipos de base de datos estándar para los campos o los tipos a medida son una buena idea para solucionar por adelantado.

Como parte de su documentación, incluya una lista de lo que no debe hacer y otra para la aplicación que incluya sus cursores de funcionalidad odiados preferidos.

Revísalo regularmente.

El estándar más importante es: no tiene una base de datos por defecto. Encuentro a demasiados desarrolladores cogiendo una base de datos para proyectos donde la vida hubiera sido mucho más fácil sin una (al menos aún). Es solo una herramienta en la caja de herramientas, y no todos los problemas son un clavo.

El uso inapropiado de una base de datos conduce a modelos de dominio anémicos, código mal comprobable y problemas de rendimiento innecesarios.

En MS-SQL, siempre he tenido objetos propiedad de dbo., Y prefijo llamadas a esos objetos con dbo.

Demasiadas veces he visto a nuestros desarrolladores preguntarse por qué no pueden llamar a sus objetos que ellos poseen de manera inadvertida.

Estándares de nombres consistentes. Tener a todos en la misma página, utilizando el mismo formato (ya sea Camel Case, prefijos específicos, etc.) ayuda a mantener un sistema con precisión.

Estoy de acuerdo con casi todo lo que has puesto allí, excepto el # 5. A menudo uso prefijos para tablas y procedimientos almacenados porque los sistemas que desarrollamos tienen muchas áreas funcionales diferentes, por lo que tendré que prefijar las tablas y sprocs con un identificador que les permita agruparse muy bien en Management Studio en función de qué área pertenecen a.

Ejemplo: cjso_Users, cjso_Roles, y luego tienes routing_Users, routing_Roles. Esto puede sonar como la replicación de datos, pero en realidad las dos tablas de usuario / roles diferentes son para funciones completamente independientes del sistema (cjso sería para la aplicación de comercio electrónico basada en el cliente mientras que el enrutamiento sería para empleados y distribuidores que usan el enrutamiento sistema).

Evite convenciones de abreviaturas absurdas, como diccionarios completos de abreviaturas que fomenten activamente monstruosidades como EMP_ID_CONV_FCTR_WTF_LOL_WAK_A_WAK_HU_HU. Esta regla está inspirada en un conjunto real de pautas que he visto antes.

La cláusula WITH realmente ayuda a dividir las consultas en partes manejables.

También ayuda a la eficiencia en los planes de ejecución de las consultas.

Me gusta nuestra convención de nombres de tablas:

People Table PEO_PersonID PEO_FirstName ...

Lo que ayuda a que las consultas más grandes sean un poco más legibles. y las uniones tienen más sentido:

Select * -- naughty! From People Join Orders on PEO_PersonID = ORD_PersonID --...

Supongo que más que lo que es la convención de nomenclatura, es la consistencia de la denominación.

Mis estándares para Oracle son:

Las palabras clave siempre están en MAYÚSCULAS;
Los nombres de los objetos de la base de datos siempre están en minúsculas;
Los subrayados reemplazarán los espacios (es decir, no habrá convenciones de casos de camello que sean comunes en, por ejemplo, SQL Server);
Las claves primarias casi siempre se llamarán ''id'';
La integridad referencial se hará cumplir;
Los valores enteros (incluidos los ID de tabla) generalmente siempre serán NUMBER (19,0). La razón de esto es que esto encajará en un entero con signo de 64 bits lo que permite usar el tipo largo de Java en lugar del BigInteger más torpe;
A pesar del término incorrecto de agregar "_número" a algunos nombres de columna, el tipo de dichas columnas será VARCHAR2, no un tipo de número. Los tipos de número están reservados para las claves primarias y las columnas sobre las que realiza operaciones aritméticas;
Siempre uso teclas técnicas principales; y
Cada tabla tendrá su propia secuencia para la generación de claves. El nombre de esa secuencia será _seq.

Con SQL Server, la única modificación es usar camel case para nombres de objetos de base de datos (es decir, PartyName en lugar de party_name).

Las consultas tenderán a escribirse en varias líneas con una cláusula o condición por línea:

SELECT field1, field2, field2 FROM tablename t1 JOIN tablename2 t2 ON t1.id = t2.tablename_id WHERE t1.field1 = ''blah'' AND t2.field2 = ''foo''

Si la cláusula SELECT es lo suficientemente larga, la dividiré en un campo por línea.

Poniendo todos los comentarios juntos en una lista.

Naming Standards

Los esquemas se nombran por área funcional (productos, pedidos, envío)
Sin notación húngara: no hay nombres de tipos en los nombres de los objetos (no strFirstName)
No use palabras clave registradas para nombres de objetos
Sin espacios ni caracteres especiales en los nombres de los objetos (Alphanumber + Underscore son las únicas cosas permitidas)
Nombra objetos de forma natural (FirstName en lugar de NameFirst)
El nombre de la tabla debe coincidir con el nombre de la clave principal y el campo Descripción (SalesType - SalesTypeId, SalesTypeDescription)
No prefijo con tbl_ o sp_
Código de nombre por nombre de objeto (CustomerSearch, CustomerGetBalance)
CamelCase nombres de objetos de base de datos
Los nombres de las columnas deben ser singulares
Los nombres de tabla pueden ser plurales
Dar nombres comerciales a todas las restricciones (MustEnterFirstName)

Tipos de datos

Use el mismo tipo de variable en las tablas (código postal: numérico en una tabla y varchar en otra no es una buena idea)
Use nNVarChar para obtener información del cliente (nombre, dirección (es)) etc. Nunca se sabe cuándo puede ir a multinacional

En codigo

Palabras clave siempre en MAYÚSCULAS
Nunca use uniones implícitas (sintaxis Comma) - siempre use explícita UNIÓN ÚNICA / UNIÓN EXTERIOR
One JOIN por línea
Una cláusula WHERE por línea
Sin bucles: reemplácelo con lógica basada en conjunto
Utilice formas abreviadas de nombres de tabla para alias en lugar de A, B, C
Evite los desencadenantes a menos que no haya ningún recurso
Evite los cursores como la peste (lea http://www.sqlservercentral.com/articles/T-SQL/66097/ )

Documentación

Crear diagramas de base de datos
Crea un diccionario de datos

Normalización e integridad referencial

Use claves primarias de una sola columna tanto como sea posible. Use restricciones únicas cuando sea necesario.
La integridad referencial siempre será aplicada
Evitar EN ELIMINAR CASCADA
OLTP debe ser al menos 4NF
Evalúa cada relación de uno a muchos como una relación potencial de muchos a muchos
Claves primarias no generadas por el usuario
Crear modelos basados en inserción en lugar de basados en actualizaciones
PK a FK debe ser del mismo nombre (Employee.EmployeeId es el mismo campo que EmployeeSalary.EmployeeId)
Excepto cuando hay una unión doble (Person.PersonId se une a PersonRelation.PersonId_Parent y PersonRelation.PersonId_Child)

Mantenimiento: ejecutar scripts periódicos para encontrar

Esquema sin tabla
Registros huérfanos
Tablas sin claves primarias
Tablas sin índices
UDF no determinista
Copia de seguridad, Copia de seguridad

Sé bueno

Se consistente
Corregir errores ahora
Lea el estilo de programación SQL de Joe Celko (ISBN 978-0120887972)

Si una base de datos es para una aplicación en particular, tenga una tabla de versiones para que las versiones de la base de datos se puedan comparar con las versiones del código (entre otras razones).

Siempre trato de no usar el tipo en el nombre del campo - "sPrimer Nombre", "sLastName" o "iEmployeeID". Si coinciden al principio, si algo cambia, no estarán sincronizados, y es un gran dolor de cabeza cambiar esos nombres más adelante, ya que también tiene que cambiar los objetos dependientes.

Intellisense y las herramientas de GUI hacen que sea trivial averiguar qué tipo es una columna, por lo que no creo que sea necesario.

Sigo muchas de las mismas convenciones que otras aquí, pero quería decir algunas cosas que aún no se han dicho.

Independientemente de si le gustan los nombres en plural o los nombres singulares para sus tablas, sea consecuente. Elija uno u otro, pero no use ambos.

La clave principal en una tabla tiene el mismo nombre que la tabla, con el sufijo _PK. Las claves externas tienen el mismo nombre que su clave principal correspondiente, pero con un sufijo de _FK. Por ejemplo, la clave principal de la tabla Producto se llama Product_PK; en la tabla de pedidos, la clave externa correspondiente es Product_FK. Escogí este hábito de otro amigo del DBA y hasta ahora me gusta.

Cada vez que hago un INSERT INTO ... SELECT, aligo todas las columnas en la parte SELECT para unir los nombres de las columnas de la parte INSERT INTO para que sea más fácil de mantener y ver cómo coinciden las cosas.

Tabular SQL formateado.

select a.field1, b.field2 from any_table a inner join blah b on b.a_id = a.a_id inner join yet_another y on y.longer_key = b.b_id where a.field_3 > 7 and b.long_field_name < 2;

Parte de esto es usar nombres de alias uniformemente largos (en el ejemplo, aquí, a, b e y son todos de longitud 1).

Con este tipo de formato, puedo responder más rápidamente a preguntas comunes como, "¿qué tabla tiene el alias de ''a''?" y "¿qué campos se unen a la tabla T en la consulta?" La estructura no tarda en aplicarse o actualizarse, y creo que ahorra mucho tiempo. Pasamos más tiempo leyendo el código que escribiéndolo.

Todos escriben consultas SQL (vistas, procedimientos almacenados, etc.) en el mismo formato básico. Realmente ayuda a los esfuerzos de desarrollo / mantenimiento en el futuro.

Tomando "base de datos" para significar "producto de SQL", mi respuesta es "Demasiados para mencionar. Podría escribir todo un libro sobre el tema". Felizmente, alguien lo ha hecho.

Usamos el estilo de programación SQL de Joe Celko (ISBN 978-0120887972): "este libro es una colección de heurísticas y reglas, consejos y trucos que lo ayudarán a mejorar el estilo de programación SQL y el dominio, y para formatear y escribir de forma portátil, legible y mantenible Código SQL ".

Las ventajas de este enfoque incluyen:

el chico sabe más sobre este tipo de cosas que yo (¿hay otro libro sobre heurística SQL ?!);
el trabajo ya se ha realizado; por ejemplo, puedo darle el libro a alguien del equipo para que lo lea y lo consulte;
si a alguien no le gusta mi estilo de codificación, puedo culpar a alguien más;
Recientemente obtuve un montón de representantes en SO al recomendar otro libro de Celko :)

En la práctica, nos desviamos de las prescripciones de The Book pero sorprendentemente raramente.

Una cosa que no he visto mencionar todavía

Nunca use palabras clave de base de datos como nombres de objeto. No desea tener que calificarlos cada vez que los usa

Si escribe mal algo cuando lo crea, arréglelo tan pronto como lo note. No pase años teniendo que recordar que en esta tabla UserName es realmente Usernmae. Es mucho más fácil de arreglar cuando no hay mucho código escrito en su contra.

Nunca use uniones implícitas (la sintaxis de coma), siempre especifique las uniones.

no olvide hacer una copia de seguridad de sus bases de datos de forma regular.

La "lista de verificación" de mejores prácticas del MVP Aaron Bertrand "Mi procedimiento almacenado"

Las tablas se nombran en singular, minúsculas, sin guiones bajos, sin prefijo
Los campos también en minúsculas, sin guiones bajos, sin prefijo
Procedimientos almacenados con el prefijo "st_" (ordena muy bien)
Las vistas que se tratan como tablas no tienen prefijo
Las vistas creadas para informes especiales, etc. tienen un prefijo "v"
Las vistas indizadas creadas para el rendimiento tienen un prefijo "ixv"
Todos los índices tienen nombres útiles (sin nombres automáticos)
Fuertemente prefiera uniqueidentifier (con incremento secuencial) sobre int IDENTITY para claves sustitutas
No limite artificialmente los campos VARCHAR / NVARCHAR a 100 o 255. Déles espacio para respirar. Esto no es la década de 1980, los campos no se almacenan acolchados a su longitud máxima.
Estándar mínimo 3NF
Prefiere unir tablas a claves externas a nivel de columna: muchas suposiciones de 1: m se desafían a medida que un sistema crece con el tiempo.
Siempre use claves sustitutivas, no llaves naturales, como la clave principal. Todas las suposiciones sobre las claves "naturales" (números de seguro social, nombres de usuario, números de teléfono, códigos internos, etc.) eventualmente serán impugnadas.

Nombra procps almacenados de forma similar con el mismo prefijo, por ejemplo si tienes 3 procedimientos almacenados para Persona. De esta forma, todo para la persona se agrupa en un solo lugar y puedes encontrarlos fácilmente sin tener que buscar en todos tus dispositivos para encontrarlos.
- PersonUpdate
- PersonDelete
- PersonCreate
Haga cosas similares para las tablas cuando tenga grupos de tablas con datos relacionados. Por ejemplo:
- Facturadores
- Factura
- InvoiceLineDetails
Si tiene la opción de esquemas dentro de su base de datos, úselos. Es mucho mejor ver:
- Invoice.Header
- Invoice.Line.Items
- Invoice.Line.Item.Details
- Person.Update
- Persona.Delete
- Persona.Crear
No use disparadores a menos que no haya otro enfoque razonable para lograr ese objetivo.
Proporcione a los nombres de campo un prefijo significativo para que pueda decir de qué tabla provienen sin que alguien tenga que explicar. De esta manera, cuando vea un nombre de campo al que se hace referencia, podrá saber fácilmente de qué tabla proviene.
Utilice tipos de datos consistentes para campos que contengan datos similares, es decir, no almacene el número de teléfono como numérico en una tabla y varchar en otra. De hecho, no lo guarde como numérico, si encuentro un número de teléfono negativo, me enojaré.
No use espacios u otros caracteres oscuros en los nombres de tabla / campo. Deberían ser completamente alfanuméricos, o si tuviera mi druthers, completamente alfabéticos con la excepción del guión bajo. Actualmente estoy trabajando en un sistema heredado donde los nombres de tablas y campos contienen espacios, signos de interrogación y signos de exclamación. Me hace querer matar al diseñador todos los días!
No utilice palabras clave de sintaxis como nombres de objetos, ya que provocará dolores de cabeza al tratar de recuperar datos de ellos. Odio tener que envolver los nombres de los objetos como [índice] que son dos caracteres innecesarios. ¡No era necesario que te escribiera maldito!

Nombre todas las restricciones