I might be asking this question wrong, please let me know if there's a better way of formulating this question.
background:
I am making .net application to handle the product information for a company selling promotional "stuff". They require many variants a single product, multiple prices based on customer, price history, and much much more. I see some advantages with XML for this, but I have no experience with "complex" data-storage like this. Essentially there are 5'000 base products, some have 30 size-variants, then there are multiple colour variants for each size, variants that are for regular customers (printed logos etc.), then there are minimum 5 prices, and then these needs to have a price history.
I made something that was messy and buggy, by using loads of tables in a SQL-database, but a 50 product test, with a limited number of variants, resulted in 34 tables created, (had it making tables as needed).
Am I going at this problem the wrong way, thinking that a single database could hold so much data?
Simplified data-structure
<products>
<product name="Nice Shirt" prodno="12345">
<prices>
<retailprice val="299">
<history>
<price val="99" date="2014.09.01" />
</history>
</retailprice>
</prices>
<variants>
<colours>
<colour val="red" />
</colours>
<sizes>
<size val="XL" pricemod="0" />
</sizes>
</variants>
</product>
</products>
This is just a simplified data-structure, written in XML, to demonstrate my thinking
Bottom line
How is the better way of storing data like this? I don't expect some finished product, but I'm in dire need of a nudge, to get moving on this project.
thanks!
Frank