I've two transaction tables which will hold millions of data. I need help in designing the index and partition for these tables.
Following is the structure.. Table 01 will hold a row per transaction ( total volume 50 million)
Table 02 is SCD type 2 table and will have around 70 rows per policy key and transaction.. All the 70 records will get inserted for 1st time then only SCD 2 will happen.(approx volume 5 billion)
You have to pay a price when you store data in name-value pairs instead ot rows...
You'll find a lot pros and cons (well, mainly cons) when you search for the data model called EAV, Entity-Attribute-Value.
Good luck trying to implement this monster :-)