I've requirement where I need to store information from different sources in my system and the process that data. When I say from different sources it will be completely different table structure from all the sources and these sources will be infinite.
If I've to maintain the same table structure of source then I've to create multiple tables for each source and I'll have thousands of sources so my database will be bloated with lot of tables. All this information is accessed within my system so for each source I need to maintain mapping information with that I can query particular table to get that source data.
Hence I came with a single table structure to store that data and it looks like below - tables with column information:
TableMetaData Id, TableName, SourceId TableFieldMetaData FieldId, FieldName, TableMetaDataId TableFieldValues Id, MetaDataFieldId, Value
Example source table
Customer CustId, Name, Location 1, ABC, London
above table will be stored as below in my three tables:
TableMetaData 1, Customer, XYZSource TableFieldMetaData 1, CustId, 1 2, Name, 1 3, Location, 1 TableFieldValues 1, 1, 1 2, 2, ABC 3, 3, London
I want to know whether this is the best approach to store data or not? I know this single table will have millions of records and retrieving the data will be very tedious too. Is there any better approach for storing this kind of data? Or should I go with multiple tables approach which I mentioned initially where every source will have replica of their tables in my system.
What is the industry standard for this?