mysql - reparar - Diseño de base de datos por primera vez: ¿Estoy sobreingeniería?

reparar base de datos mysql workbench (11)

Algunas cosas vinieron a la mente:

Las mesas parecían orientadas a la presentación de informes, pero en realidad no manejaban el negocio. Pensaría que cuando un cliente se registra, básicamente se coloca un pedido para el cliente que asiste a una lista de sesiones, y ese pedido puede ser para varios empleados en una empresa. Parecería que una tabla de "pedidos" realmente estaría en el centro de su sistema e impulsaría su captura de datos y el reporte final. (Compare los documentos en papel que ha estado usando para ejecutar el negocio con el diseño de su base de datos para ver si hay una coincidencia lógica).
Las empresas a menudo no tienen divisiones. Los empleados a veces cambian de división / departamento, tal vez incluso a mitad de sesión. Las empresas a veces agregan / eliminan / renombran divisiones / departamentos. Asegúrese de que los posibles cambios en el contenido en tiempo real de sus tablas no dificulten los informes / agrupaciones posteriores. Con tantos datos de contacto divididos en tantas tablas, es posible que deba aplicar una validación de entrada de datos muy estricta para mantener sus informes significativos e inclusivos. Por ejemplo, cuando se agrega un nuevo cliente, asegúrese de que su empresa / división / departamento / ciudad coincida con los mismos valores que sus compañeros de trabajo.
El concepto de "paquetes" no está claro en absoluto.
Ya que indica que es una pequeña empresa, sería sorprendente que el rendimiento fuera un problema, teniendo en cuenta la velocidad y la capacidad de las máquinas actuales.

Fondo

Soy un estudiante de primer año de CS y trabajo a tiempo parcial para la pequeña empresa de mi padre. No tengo ninguna experiencia en el desarrollo de aplicaciones del mundo real. He escrito guiones en Python, algunos cursos en C, pero nada como esto.

Mi padre tiene un pequeño negocio de capacitación y actualmente todas las clases están programadas, grabadas y seguidas a través de una aplicación web externa. Hay una función de exportación / "informes" pero es muy genérica y necesitamos informes específicos. No tenemos acceso a la base de datos real para ejecutar las consultas. Me han pedido que configure un sistema de informes personalizado.

Mi idea es crear las exportaciones CSV genéricas e importarlas (probablemente con Python) en una base de datos MySQL alojada en la oficina cada noche, desde donde puedo ejecutar las consultas específicas que sean necesarias. No tengo experiencia en bases de datos pero entiendo lo más básico. He leído un poco sobre la creación de bases de datos y formularios normales.

Podemos comenzar a tener clientes internacionales pronto, así que quiero que la base de datos no explote si / cuando eso sucede. También tenemos un par de grandes corporaciones como clientes, con diferentes divisiones (por ejemplo, la empresa matriz de ACME, la división de atención médica de ACME, la división de cuidado corporal de ACME)

El esquema que he encontrado es el siguiente:

Desde la perspectiva del cliente:
- Clientes es la mesa principal.
- Los clientes están vinculados al departamento para el que trabajan.
  - Los departamentos se pueden dispersar por un país: RR.HH. en Londres, Marketing en Swansea, etc.
  - Los departamentos están vinculados a la división de una empresa.
- Las divisiones están vinculadas a la empresa matriz.
Desde la perspectiva de las clases:
- Sesiones es la mesa principal.
  - Un profesor está vinculado a cada sesión.
  - Se le da un statusid a cada sesión. Ej. 0 - Completado, 1 - Cancelado
  - Las sesiones se agrupan en "paquetes" de un tamaño arbitrario
- Cada pack se asigna a un cliente.

Yo "diseñé" (más bien garabateé) el esquema en una hoja de papel, tratando de mantenerlo normalizado a la tercera forma. Luego lo conecté a MySQL Workbench y lo hizo todo muy bonito para mí:
( Haga clic aquí para ver el gráfico a tamaño completo )

texto alt http://maian.org/img/schema.png

Consultas de ejemplo que estaré ejecutando

Los clientes con crédito que aún quedan están inactivos (aquellos sin una clase programada en el futuro)
¿Cuál es la tasa de asistencia por cliente / departamento / división (medida por la identificación del estado en cada sesión)?
¿Cuántas clases ha tenido un profesor en un mes?
Marcar los clientes que tienen un bajo índice de asistencia
Informes personalizados para departamentos de recursos humanos con índices de asistencia de personas en su división.

Pregunta (s)

¿Esto es una ingeniería excesiva o me dirijo de la manera correcta?
¿La necesidad de unir varias tablas para la mayoría de las consultas resultará en un gran éxito de rendimiento?
He agregado una columna de "sesión de última sesión" a los clientes, ya que probablemente será una consulta común. ¿Es esta una buena idea o debo mantener la base de datos estrictamente normalizada?

Gracias por tu tiempo

Algunas respuestas más a sus preguntas:

1) Usted está casi en el blanco para alguien que se está acercando a un problema como este por primera vez. Creo que los indicadores de otros sobre esta pregunta hasta ahora casi lo cubren. ¡Buen trabajo!

2 y 3) El impacto en el rendimiento que tomará dependerá en gran medida de tener y optimizar los índices correctos para sus consultas / procedimientos particulares y, lo que es más importante, el volumen de registros. A menos que esté hablando de más de un millón de registros en sus tablas principales, parece estar en camino de tener un diseño lo suficientemente convencional como para que el rendimiento no sea un problema en el hardware razonable.

Dicho esto, y esto se relaciona con su pregunta 3, con el comienzo que tiene, probablemente no debería preocuparse demasiado por el rendimiento o la hipersensibilidad a la ortodoxia de normalización aquí. Este es un servidor de informes que está creando, no un backend de aplicación basado en transacciones, que tendría un perfil muy diferente con respecto a la importancia del rendimiento o la normalización. Una base de datos que respalde una aplicación de registro y programación en vivo debe tener en cuenta las consultas que demoran segundos en devolver los datos. Una función de servidor de informes no solo tiene más tolerancia para consultas complejas y largas, sino que las estrategias para mejorar el rendimiento son muy diferentes.

Por ejemplo, en un entorno de aplicación basado en transacciones, sus opciones de mejora de rendimiento podrían incluir refactorizar sus procedimientos almacenados y estructuras de tablas hasta el noveno grado, o desarrollar una estrategia de almacenamiento en caché para pequeñas cantidades de datos solicitados comúnmente. Sin duda, en un entorno de informes puede hacer esto, pero puede tener un impacto aún mayor en el rendimiento mediante la introducción de un mecanismo de instantáneas donde se ejecuta un proceso programado y almacena informes preconfigurados y sus usuarios acceden a los datos de instantáneas sin estrés en su nivel de db en una base por solicitud.

Todo esto es una perorata de largo aliento para ilustrar que los principios de diseño y los trucos que empleas pueden diferir dado el rol de la base de datos que estás creando. Espero que sea de ayuda.

La mayoría de las cosas ya se han dicho, pero creo que puedo agregar una cosa: es bastante común que los desarrolladores más jóvenes se preocupen demasiado por el rendimiento desde el principio, y su pregunta sobre cómo unirse a las tablas parece ir en esa dirección. Este es un anti-patrón de desarrollo de software llamado '' Optimización prematura ''. Intenta eliminar ese reflejo de tu mente :)

Una cosa más: ¿cree que realmente necesita las tablas de ''ciudades'' y ''países''? ¿No sería suficiente tener una columna de ''ciudad'' y ''país'' en la tabla de departamentos para sus casos de uso? Por ejemplo, ¿su aplicación necesita enumerar los departamentos por ciudad y por país?

Los únicos cambios que haría serían:
1- Cambie su VARCHAR a NVARCHAR, si es posible que se internacionalice, es posible que desee unicode.

2- Cambie su identificación de usuario a GUID (identificador único) si es posible (esto podría ser mi preferencia personal). Suponiendo que eventualmente llegue al punto donde tiene múltiples entornos (dev / test / staging / prod), es posible que desee migrar datos de uno a otro. Tener ID de GUID hace esto mucho más fácil.

3- Tres capas para su empresa -> División -> La estructura del departamento puede no ser suficiente. Ahora, esto podría ser una ingeniería excesiva, pero podría generalizar esa jerarquía de modo que pueda admitir n niveles de profundidad. Esto hará que algunas de sus consultas sean más complejas, por lo que puede que no valga la pena el compromiso. Además, podría ser que cualquier cliente que tenga más capas se pueda "meter" fácilmente en este modelo.

4- También tiene un estado en la tabla de clientes que es un VARCHAR y no tiene enlace a la tabla de estados. Espero un poco más de claridad en cuanto a lo que representa el estado del cliente.

Los siguientes comentarios se basan en el rol de especialista en inteligencia de negocios / informes y gerente de estrategia / planificación:

Estoy de acuerdo con la dirección de Larry arriba. En mi humilde opinión, no está demasiado diseñado, algunas cosas se ven un poco fuera de lugar. Para que sea sencillo, etiquetaría al cliente directamente a una ID de empresa, Descripción de departamento, Descripción de división, ID de tipo de departamento, ID de tipo de división. Utilice el ID de tipo de departamento y el ID de tipo de división como referencias para las tablas de búsqueda y los campos de análisis / informes internos para una consistencia a largo plazo.
La tabla de Paquetes contiene la columna "Crédito", ¿no debería estar realmente vinculada a la tabla de la base de Clientes, de modo que si en muchos paquetes se puede ver cuánto crédito se debe para las clases futuras? La aplicación puede ocuparse del cálculo y almacenarlo centralmente en la tabla del Cliente.
La información de la compañía podría usar muchos más campos, incluida la dirección / teléfono / etc. información. También estaría preparado para agregar columnas D&B "DUN" (Sitio / Sucursal / Último) a largo plazo, Dun and Bradstreet (D&B) tiene un enorme catálogo de compañías y más adelante encontrará que su información es muy útil. para la presentación de informes / análisis. Esto se encargará del problema de división múltiple que mencione y le permitirá acumular su jerarquía para subdivisiones / divisiones / sucursales / etc. de grandes cuerpos.
No menciona la cantidad de registros con los que trabajará, lo que podría implicar prepararse para una gran iniciativa de desarrollo que podría haberse producido con mayor rapidez y muchos menos dolores de cabeza con el software de "informes" preenvasado. Si no está tratando con una gran base de datos (<65000) filas, asegúrese de que MS-Access, OpenOffice (Base) o las soluciones de desarrollo de informes / aplicaciones relacionadas no puedan hacer el truco. Yo uso bastante el software APEX gratuito de Oracle, viene con su base de datos gratuita Oracle XE, solo descárguelo de su sitio.
FYI - Información de informes: para bases de datos grandes, normalmente tiene dos instancias de base de datos a) base de datos de transacciones para registrar cada registro detallado. b) Base de datos de informes (data mart / data warehouse) alojada en una máquina separada. Para más información, busque en google Star Schema y Snowflake Schema.

Saludos.

No está sobre diseñado, así es como abordaría el problema. Unirse está bien, no habrá mucho éxito en el rendimiento (es completamente necesario a menos que des-normalice la base de datos, ¡lo cual no se recomienda!). Para los estados, vea si puede usar un tipo de datos de enumeración para optimizar esa tabla.

No. Parece que estás diseñando con un buen nivel de detalle.

Creo que los países y las empresas son realmente la misma entidad en su diseño, al igual que las ciudades y divisiones. Me desharía de las tablas de Países y Ciudades (y de Cities_Has_Departments) y, si fuera necesario, agregaría un IsPublicSector de bandera booleana a la tabla de Compañías (o una columna CompanyType si hay más opciones que simplemente Sector privado / Sector público).

Además, creo que hay un error en el uso de la tabla Departamentos. Parece que la tabla Departamentos sirve como referencia a los distintos tipos de departamentos que puede tener cada división de clientes. Si es así, debería llamarse DepartmentTypes. Pero sus clientes (que son, supongo, asistentes) no pertenecen a un departamento TIPO, pertenecen a una instancia de departamento real en una empresa. Tal como está ahora, sabrá que un cliente determinado pertenece a un departamento de recursos humanos en algún lugar, ¡pero no a cuál!

En otras palabras, los clientes deben estar vinculados a la tabla a la que llama Divisions_Has_Departments (pero que yo llamaría simplemente Departamentos). Si esto es así, entonces debe colapsar Ciudades en Divisiones como se explicó anteriormente si desea usar la integridad referencial estándar en la base de datos.

Por cierto, vale la pena señalar que si ya estás generando CSV y quieres cargarlos en una base de datos mySQL, LOAD DATA LOCAL INFILE es tu mejor amigo: http://dev.mysql.com/doc/refman/5.1/en/load-data.html . También vale la pena examinar Mysqlimport, y es una herramienta de línea de comandos que básicamente es un buen envoltorio para el archivo de datos de carga.

Quiero abordar solo la preocupación de que unirse a varias tablas generará un impacto en el rendimiento. No tenga miedo de normalizarse porque tendrá que hacer uniones. Las uniones son normales y se esperan en bases de datos relacionales y están diseñadas para manejarlas bien. Necesitará establecer relaciones PK / FK (para la integridad de los datos, esto es importante tener en cuenta en el diseño), pero en muchas bases de datos, los FK no se indexan automáticamente. Ya que se usarán en las combinaciones, definitivamente querrá comenzar indexando el FKS. Las PK generalmente obtienen un índice en la creación, ya que tienen que ser únicos. Es cierto que el diseño del datawarehouse reduce el número de uniones, pero generalmente uno no llega al punto de almacenamiento de datos hasta que uno tiene millones de registros necesarios para acceder a ellos en un informe. Incluso entonces, casi todos los almacenes de datos comienzan con una base de datos transaccional para recopilar los datos en tiempo real y luego los datos se trasladan al almacén de forma programada (por noche o mensualmente o lo que sea la necesidad comercial). Por lo tanto, este es un buen comienzo, incluso si necesita diseñar un almacén de datos más adelante para mejorar el rendimiento de los informes.

Debo decir que su diseño es impresionante para un estudiante de primer año de CS.

Tienes la idea correcta. Sin embargo, puede limpiarlo y eliminar algunas de las tablas de mapeo (tiene *).

Lo que puede hacer está en la tabla Departamentos, agregue CityId y DivisionId.

Además de eso, creo que todo está bien ...

Trabajé en el dominio de entrenamiento / escuela y pensé que me gustaría señalar que generalmente hay una relación M: 1 entre lo que ustedes llaman "sesiones" (instancias de un curso determinado) y el curso en sí. En otras palabras, su catálogo ofrece el curso ("Spanish 101" o lo que sea), pero puede tener dos instancias diferentes durante un solo semestre (Tu-Th enseñado por Smith, Wed-Fri enseñado por Jones).

Aparte de eso, parece un buen comienzo. Apuesto a que encontrará que el dominio del cliente (gráficos que conducen a "clientes") es más complejo de lo que ha modelado, pero no exagere con eso hasta que tenga algunos datos reales que lo guíen.