In general a major concern for this type of problem is how often new fields will be added. In most places (and in my experience) new fields are added often. Business needs always change and grow, and new information will need to be captured. It is difficult to ensure that new fields won't be required/requested in the future. So most places tend to use an approach that is slightly less performance optimized but allows for flexibility of adding new fields with little to no changes on the database.
In general I would recommend a
Key-Value Pair approach on this problem. I don't believe my answer is any different than Phill W.'s but maybe a different way of describing it will help.
In General the value of a
Key-Value Pair is flexibility to add new "fields" without making schema changes. This is done by effectively
un-pivoting the table.
For example Table 1/2 as you described in the question would be converted into something like (where JobNumber and FieldName is a unique combination. That wasn't a unique combination in the source table in the question, so I left everything there but I the intention is that the combination is unique):
Table 3 is then re-formatted into something like (where JobNumber, Country, FieldName is unique):
Though this is less efficient than having a single row, per JobNumber or JobNumber/Country and 1 column per field. It is much easier to change when a new field is added. A new field, is simply a different value appearing in the the
FieldName column. In theory, a generic procedure is able to accept a new value for
FieldName and start loading that value without having to make any Database Side code changes.
This is beneficial because, in my experience, new fields will come. And I would rather have a slightly less effective storage process and spend a little more effort in my standard procedures to work with multiple rows. Than having to add a column to the table, and having to modify the exiting procedure to return and populate this new column, every time a new field is requested.
From here, would then then want to normalize out what items make sense. It looks like to me, that we can normalize out:
- Field Name into a "ColumnField" table (in both Table 1/2 and Table 3. Potentially using the same "ColumnField" table for both Table 1/2 and Table 3, or two different "ColumnField" tables.)
- County into a Country table (in Table 3)
- Field Value into a "FieldValue" table (in Table 3)
NOTE: It does not look like to me there is a small set of unique values for the "FieldValue" in Table 1/2. For that reason I think it makes more sense to store the values on there directly instead of trying to normalize that field out somewhere.
This then creates a Table 1/2 that looks something like (where JobNumber and FieldId is unique. Again this was not unique in the source table, and I left the duplicates in but the uniqueness should be the intent).
And a Table 3 that look something like (where JobNumber, CountryId, FieldId is unique):
From here you could normalize out the combination of CountryId and FieldId in Table 3 into some separate Country-Field table (like Phill W. suggested) and have a final result of something like (where JobNumber and ContryFieldId is Unique).
This does create a duplication of Id values in this CountryField table, but it allows you to reference a single Id instead of 2 in your
Key-Value Pair table. So you are duplicating the IDs 400 times in one place, to save duplicating the IDs on your transactional table, which I imagine would be hundreds of thousands of rows, if not millions or billions.:
One other alternative to a
Key-Value Pair is to store the data on the table in either an XML or JSON blob. Which would turn into something like below:
I personally like this method less, because the JSON (or equivalent XML) is more difficult to work with in the Database because of the extra parsing and functions required. The benefit is you have less rows overall, which I think is only a benefit if there are company or project policies against duplication of value at almost all costs.