There are a few things you want to factor into your design:
1. Measurements Need a Timestamp
Make sure all of your measurements have an indication of:
- Scalar Value
- Unit of Measurement
- Date and Time the Measurement was Taken
This will allow you to work with measurements that need time-dependent conversion calculations.
2. Units of Measure Have Attributes
Each unit of measure has a few different attributes. The obvious ones are indicative, like a code and maybe a descriptive name. There are also a couple of critical other attributes to keep for each unit of measure. (i) Unit Type and (ii) Conversion Factor to the Base Unit.
The first tells you whether your unit of measure is a length, a weight, energy, power, currency, etc. etc. It should also tell you what the base unit of measure is. You should pick exactly one for each unit type. You can use things like kWh if you like, but I'd stick to the base SI units (as applicable) if I were you.
The second tells you what your unit of measure needs to be multiplied by to get it to the base. I mentioned that this is an attribute of your UOM, but in fact it needs to be in a child table. The business key of the child table that holds this base conversion factor is the combination of the UOM, its base unit type and a date/time. I would keep both an effective and an expiry date/time on the base conversion factor table. This allows you to quickly find the right rate that applies at any particular point in time. If it happens to be a rate that doesn't change, that's OK. Just use a min-collating effective date and a max-collating expiry date for the one record.
3. Trying to Table-Drive Everything Will Make You Nuts
The last piece of the puzzle is determining the calculation for moving from one kind of unit to another kind of unit. You could try to table-drive this kind of calculation but in the end the tricky ones are going to make the design so general (read complicated and slow) that it will be impractical. Instead, create a code-table of conversion calculations and use it to link one kind of Unit Type to another kind of Unit Type. Perform the actual calculations in some code somewhere. Which piece of code you use for any given conversion is what the code table tells you. How the calculation is performed is just in the code. You can have one calculation each for the various easy things, like area needs two lengths and volume needs three lengths as well as the harder ones like work needs energy and time.
When you get the details of your design figured out you should blog it and come back here to post a link!
I was thinking of allowing each table to have its own file so that no
matter if I'm joining on 2, 3, 4, 5 or more tables, each table will
always be read using a separate thread and the structure of each file
will be closely aligned with the table contents, which would hopefully
minimise fragmentation and make it faster for SQL Server to add to the
contents of any given table
What the heck are you talking about? Not sure where you got your information from, but you should certainly discard that source. Nothing from what you assume here is actually correct.
If you want to read a good discussion of SSD performance for SQL Server there are several blog series out there. As usually, Paul Randal's one is the top read:
Brent also has a nice presentation on the topic: SQL on SSDs: Hot and Crazy Love and there are more out there.
Going through all these presentations you will quickly notice that they all focus on writes since this is where SSDs performance comes into picture. Your post wording is almost entirely about reads, which is a different topic. If reads are your pain point then you should be talking about RAM, not about SSDs, and about proper indexing and querying strategies.
Best Answer
Since you mention hundreds of columns I would consider an EAV design. While Joe Celko warns against this, I think it may be applicable in your use case. It sounds like all of your "amounts" are numbers, so you would avoid the casting issues Joe describes and the need to make every "value" a string. It will work even better if all the amounts are whole numbers, but can work also if some are decimal. Given the Units of Measure, you could go one step further and implement an "universal data model" style model based off this article by David Hay and also outlined in his book Data Model Patterns: Conventions of Thought. This model has the additional advantage of configuring which "amounts" apply to which "things" if you need that. One additional step shown in the book on page 162 is a Unit of Measure Conversion table that you can use to convert between the different Units of Measure. Here is an example:
This says that to convert from Kg to Lb the first step is to multiply Kg by 2.2. There is also a constant if a conversion must also include a constant value, and the ability to create multiple steps. So when converting say Celsius to Fahrenheit you multiply Celsius by 1.8 and then add 32. The key would be the from UOM, the to UOM, and the Calculation Step.
That is my 2 cents worth. I hope these references give you some good food for thought should you ever get the chance to do a reboot on the current design.