I have some groups tables and we need to load data in that group tables from xml files that contain column names and data.
The column name is actually index of some main column like activity_col1,activity_col2 and so on and not fixed every time, there is possibility that
same table file contains 1000 columns sometimes and 10 column values some time also there is maximum limit is also defined so no file will contain more than
2000 column per group.
so I need to design a table that is the best possible solution for this also I need to do the aggregation of column values. The files contain min level data and i need to store this data in min table and after that this min data need to be aggregated in an hour, day, week and month.
if I create max columns in all tables but data will not come every time in all columns so this design seems not good because most of the values will be null.
if I insert column name as rows in column_name column and values against each column value in values column then aggregation will be a tedious task for me
and it will impact performance.
I'm not sure why you say that. The use of such EAVs can be quite simple and aggregations of the data will certainly be simpler than using any form of the original XML.
That, notwithstanding, it would be much better if you were to figure out what type of data you receive from various sources and make the specifically appropriate tables to accommodate the actual data rather than trying to make "catch all tables".
I am not trying to catch all tables, the purpose is to catch all the columns and only columns that are coming for particular tables. The number of columns for each tables can be increased or decreased any time in file that's why i want a dynamic approach that load data and when we try to fetch it could be easier for me to fetch that data in sql queries.
Ok. Then the latest thing you have in your first post suggests an EAV result which would need to be dynamically pivoted into a new table. That will not slow performance as much as you think and will provide you with the precise functionality that you require. If I were stuck with scenario, then that's the way I'd resolve it.
There may be a possibility of dynamically creating shredder code that would eliminate that possibility but I'd have to see the XML to make that determination.
Of course, I'd also have to hunt down and beat the hell out of the person sending me data in tag bloated XML format to begin with.
You said "if I insert column name as rows in column_name column and values against each column value in values "...
That would actually be an NVP so I'll correct myself and say the NVP will be fine. It won't slow you down or cause problems except for "DRI" (Declared Referentail Integrity or things like FKs {Foreign Keys}.
