Modelo de datos: ¿muchas columnas o muchos registros?

[jasoto]

Hola, tengo una consulta para quien quiera y pueda orientarme: Tengo que crear una tabla que en la que deben insertarse por cada carga mensual un cierto numero de campos, que se “multiplican” por 4 diferentes categorías, cada una de las cuales tiene varios posibles valores. Mi duda es si crear una tabla con muchas columnas para crear menos registros o al revés, crear menos columnas, aunque a costa de tener que guardar más registros. Me explico y detallo:
La opción extrema en un sentido (mínimo numero de columnas y máximo numero de registros) sería crear una tabla con 6 columnas (PK + 4 para cada una de las categorías + valor). Los posibles valores de cada categoría son Categ_A = 8; Categ_B = 4; Categ_C = 4; Categ_D = 2), lo cual supondría tener que guardar el producto cartesiano de sus posibles valores, es decir, mínimo 70.000 * 8 *4 *4 *2  17.920.000 registros por carga, es decir, parametrizándola al máximo, para simplificar el Modelo. Ejemplo:
PK CATEG_A CATEG_B CATEG_C CATEG_D VALOR
1 A1 B1 C1 D1 A1B1C1D1
2 A1 B1 C1 D2 A1B1C1D2
3 A1 B1 C2 D1 A1B1C2D1
4 A1 B1 C2 D2 A1B1C2D2
…
La opción más opuesta (mínimo numero de columnas y máximo numero de registros) sería que la tabla a crear tuviera 257 columnas (PK + (8 * 4 * 4 *2)) es decir, todas las posibles combinaciones de los valores de las categorias, lo cual serviría para tener que guardar 70.000 registros por carga. Ejemplo:
PK CATEG_A1B1C1D1 CATEG_A1B1C1D2 CATEG_A1B1C2D1 CATEG_A1B1C2D2 …
1 A1B1C1D1 A1B1C1D2 A1B1C2D1 A1B1C2D2 …
…

Obviamente, hay múltiples opciones intermedias, como por ejemplo crear una tabla de 33 columnas (PK + (4 * 4 * 2), es decir, las combinaciones de 3 de las 4 categorías, para albergar 560.000 registros por carga. Ejemplo:
PK CATEG_A CATEG_B1C1D1 CATEG_B1C1D2 CATEG_B1C2D1 CATEG_B1C2D2 …
1 A1 B1C1D1 B1C1D2 B1C2D1 B1C2D2 …
2 A2 B1C1D1 B1C1D2 B1C2D1 B1C2D2 …

Otros datos que pueden ayudaros a evaluar mi consulta:
Frecuencia y tipo de las cargas: 1 vez / mes, en proceso batch nocturno, que insertaría cada vez de 70.000 a 17.920.000 registros, según lo dicho.
Indices: podría crear los indices que fueran necesarios.
Consultas On-Line: la mayoría de las consultas se harían sobre uno de los 8 posibles valores de la CATEG_A.

¿me he explicado? ¿necesitáis más información para orientarme?

[matanga]

En estos casos puedes separar el análisis en tres partes, almacenamiento, rendimiento y código sql, es decir:

1. Almacenamiento.

1.1. El espacio final de la tabla será aproximadamente el mismo teniendo los datos en columnas o en filas, por aquí no hay mucho para hacer.

1.2. Almacenar los datos en columnas genera filas de mayor tamaño, por lo que tendrás que definir un tamaño de bloque mayor al estándar (8k) para evitar filas anidadas o migradas, esto puede ser un poco molesto en ciertas tareas administrativas (export / import, backups, etc), y también puede afectar el rendimiento, piensa que un bloque es la unidad mínima que gestiona el motor para las operaciones de lectura y escritura, y no es lo mismo subir a memoria un bloque de 32k que un bloque de 8k.

1.3. Almacenar los datos en filas genera índices de mayor tamaño, pero es más eficiente para implementar técnicas como particionamiento de tablas, y también para alocar o liberar espacio durante el proceso de carga o depuración de datos.

2. Rendimiento.

2.1. Almacenar los datos en columnas favorece el rendimiento si por cada fila se leen o modifican la mayoría de las columnas, si la cantidad de filas que se leen o modifican en cada consulta es baja, o si reduce la cardinalidad de las columnas utilizadas en el where y la mayoría de las consultas son por rangos pequeños (where id=1 o where id between 1 and 10).

2.2. Almacenar los datos en filas favorece el rendimiento si por cada fila se leen o modifican la minoría de las columnas, si la cantidad de filas que se leen o modifican en cada consulta es alta, o si la mayoría de las consultas son por rangos grandes (where id between 1 and 1000).

3. Código SQL, aquí no hay mucho que analizar, almacenar los datos en columnas siempre requiere de mayor cantidad de consultas, excepto el caso de select * from tabla.

Saludos

[jasoto]

OK, gracias, me ha sido de gran ayuda tu respuesta. Creo que finalmente optaré por un término medio, tirando a guardar la info más en registros que en columnas.