The challenge:

The company has 280+ lists in multiple systems across the enterprise (SharePoint, Access, Excel, Home Built Web App, Accounting System, etc.) they want me to move all of the data into the warehouse.  They are not going to eliminate the lists, just have the data moved to the warehouse every night.

Only a few of the lists are related most are standalone or used as reference tables for other apps.

The record lengths are all over the place some just 3 columns, others have 50+ columns.

The volumes like the record length vary from a few records to hundreds, with only a couple reaching thousands.

All of the lists have a natural key, either integer value, or short char.

My thought was to create an EAV like structure but having multiple columns values for each data type, Integer1-25, DateTime1-25, String1-25, etc.).  I already use an EAV structure for reference views within the warehouse; so I already have the Meta structure to manage the process.

Call it an EAR (entity attribute record) structure; should make moving data into it easier.

Table structure example:

MetaID, IntKey, CharKey, Dates, Integers, Varchars, Decimals, etc.

With the IntKey and CharKey I thought I could then build materialized views index on whichever the type the source list uses.

Your thoughts, help, advice are appreciated. Even if you think I’ve either smoked too much or not enough.

Regards,

Why bother with the different datatypes. Just make the ID column a varchar and concert the ints to varchar when you load them? Will you be joining that column wiht other tables? You can use an IDENTITY for a surrogate INT key if you want but in any case you would need a combo key that denotes the List and the ID within that list.