Which is best DB design
235699Jul 15 2003 — edited Jul 17 2003Hi All
This is a question regarding database design really.
I have my flat files coming in from 3 different source systems say X , Y, Z.
Each flat file will have 3 types of records say type A , B and C
Now X may have 10 different fields of type A.
Y may have 15 different fields of type A (Some of the fields may be same but lengths of datatypes might change)
Z may have 20 15 different fields of type A (Some of the fields may be same but lengths of datatypes might change)
Similarly for records of type B and C.
My question is should I have 9 different tables for X, Y, and Z (ie 3 * 3)
Or just 3 tables based on the record type ie. Common for all source system – In which case I may need 20 fields for table of type A even though X might use just 10 of them.
Initially the data has to be loaded into temporary tables and then after transformation into permanent tables after which reports need to be generated.
The 3 source systems are not related to each other during loading, transformation or reporting
Thanks
Ashwin N.