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?
Best Answer
What you are proposing is a variant of the so-called "Entity-Attribute-Value" or EAV design pattern.
Aaron Bertrand has an excellent article on the advantages and pitfalls of this design over on SQLBlog.org.
Many people trash-talk EAV, however if done correctly it can be the easiest way to manage diverse sets of data, and is in-fact designed particularly well for your scenario.