Cita: pero la tabla material_eliminados la tengo que tener porque contiene muchos campos.
¿Muchos campos provenientes de qué?
Sin saber cómo es el diseño del sistema, no es fácil darte consejos.
Cita: me encuentro en el trabajo con muchas situaciones que no se que es mejor si duplicar un campo o hacer inner joins porque si estas hablando de tablas que tiene muchos registros igual en vez de tener que hacer tres inner joins para tener la info de un solo campo te conviene tener ese campo en la tabla principal.
Eso no es necesariamente cierto.
Que tengas muchos registros, sólo se puede decir si los registros son centenares de millones. Centenaress de miles no son problema, por lo que sin mas datos no estoy seguro de qué problemas te causan.
Además, como te dije, duplicar datos en otras tablas no es una buena idea porque aumenta el riesgo de inconsistencia, y en ciertos contextos, el proceso de verificación de consistencia se come todas las ventajas que puedes lograr duplicando esos datos. ¿Entonces qué ventajas tendría?
Por otro lado, el que tengas que hacer un triple INNER JOIN ni siquiera es un problema... Hay consultas en algunas bases que trabajo que tienen hasta 16 INNER JOINs juntos, y eso no significa ni lentitud, ni problemas, porque existe una excelente optimización de las mismas.
Antes de ponerse a duplicar datos, es conveniente ver si el problema de acceso a la información se puede resolver usando vistas, e incluso funciones.
Hay muchos recursos posibles, sin necesidad de terminar atentando contra la consistencia y la integridad de datos.
De todos modos, como ya mencioné, sin datos más concretos acerca del sistema y el diseño de la base, no hay mucho mas que pueda decirte.
Yo soy de la idea de de no duplicar datos innecesariamente, porque he visto y trabajo a cada rato con los problemas que causa ese tipo de cosas en la empresa donde estoy, producto de la idea de "facilitar" el acceso y reducir la cantidad de INNER JOINs existentes.
¿Cuál es el resultado?
Las aplicaciones colapsan en determinados momentos porque consultas a dos tablas que deberían tener datos compatibles no los tienen... porque no hay consistencia.
A la larga, lo único que tendrás serán problemas.
En cuanto a lo de no eliminar los datos de las tablas base, es un principio que aprendes dentro de las "buenas prácticas" de DBA. No lo inventé yo.
Duda: Esa tabla de eliminados, ¿la usas para guardar más datos además de una copia del registro de la tabla origen?