I have a SQL Server table in which I need to store daily interest rate data.
Each day, rates are published for multiple time periods. For example, a 1 day rate might be 0.12%, 180 day rate might be 0.070% and so on.
I am considering 2 options for storing the data.
One option is to create columns for date, "days" and rate:
Date | Days | Rate
=========================
11/16/2015 | 1 | 0.12
11/16/2015 | 90 | 0.12
11/16/2015 | 180 | 0.7
11/16/2015 | 365 | 0.97
The other option is to store the "days" and rate via a JSON string(or XML.)
Date | Rates
=============================================================
11/16/2015 | { {1,0.12}, {90,0.12}, {180, 0.7}, {365, 0.97} }
Data only will be imported via bulk insert; when we need to delete we'll just delete all the records and re-import; there is no need for updates. So my need is mostly to read rates for an specified date or range of dates into a .NET application for processing.
I like option 2 (JSON) - it will be easier to create objects in my application; but I also like option 1 because I have more control over the data - data types and constraints.
Any similar experience out there on what might be the best approach or does anyone care to chime in with their thoughts?
I would do option 1. MS SQL Server is a relational database, and storing key:value pairs as in option 2 is not normalized, and is not an efficient for SQL Server to deal with. If you really want option 2, I would use something other than SQL Server.