Help - Database design

Question

We are implementing the new requirement in our current web application. If we discuss the database design for the new requirement, it appears to be more complex.

Requirement -

We have two webforms; the first will have input fields for about 30 fields, and the second will show the values for these fields as well as columns for up to 42 countries columns which will contain dropdown list for each of the 30 fields.

The challenge now is to construct our database so that it can accommodate the 30 field values and corresponding nation checks. There may be several nation checks for each field (max is 42). How can a table be created for this kind of situation?

Our Approach -

We are considering building three tables: one with 15 fields, another with another 15 fields, and a third for country-specific dropdown list values. One identifying column, like Job ID, will be present in all of these tables.

However, the third table will have an identification column called "Job ID," a country code column called "Country Code," and columns for all 30 fields, which are present in the previous two tables, having dropdown list values like "Yes, No, NA, NR."

Problem -

As we need to add 30 fields columns to the third table, which are already included in the first two tables, it doesn't seem to be an acceptable database architecture.

Sample data -

Table 1/2 -

Table 3 -

Sample UI for Table 3 -

If anyone can point us in the correct way for making a database design that is more optimised for our needs, that would be greatly appreciated.

Answer 1

There may be several nation checks for each field (max is 42).

Today.
Tomorrow, someone will want number 43.
It's almost guaranteed.

Database != Spreadsheet

Create a table for each of the "30" fields, each with an identifier.
Create a table for each of the "42" countries, each with an identifier.
Create a table that represents the "intersection" of the two, i.e. where an "checkbox" is ticked.

create table fields 
( id int not null ... 
, primary key id 
); 

create table countries
( id int not null ... 
, primary key id 
); 

create table field_countries
( field_id int
, country_id int 
, primary key ( field_id, country_id ) 
, foreign key field_id references fields( id )
, foreign key country_id references countries( id )
); 

Then write the code to map the contents of those tables to and from the "grid" structure that you want on screen.

If someone wants to add more "fields" or more countries? No problem.
Just add more rows into these table. You can even do it at run time, with zero down-time or maintenance overhead.

Answer 2

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.