Data from different tables into one table

Question

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?

Answer 1

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.