1

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 -

enter image description here

Table 3 -

enter image description here

Sample UI for Table 3 -

enter image description here

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.

6
  • For the various values for the "Field" fields. Do you need to do complex processing, JOINing, Filtering, etc.. on them? Or do you just need to store/retrieve them? Is the list of possible values for the various "Field" fields relatively static/small or can the user provide any random value they want? Jan 3, 2023 at 15:43
  • @KirkSaunders Thanks for checking on my question. Yes, we will need to Join the tables and the values for the countries will be small (Yes,No,NR,NA) for the respective fields. Jan 4, 2023 at 5:48
  • 1
    The key to this is thinking about the future. Do you want to have to add a column to multiple tables every time a requirement change comes in, or, add a row to one table? In short, normalize the construction. Look up the rules of normalization & database design. Based on your question. I don't have a good suggestion, not enough info. This is a great reference: amazon.com/… Jan 4, 2023 at 12:43
  • 1
    @RahulHendawe I have added an answer. Can you please review and let me know what you think. I think Phill W. and I are more or less thinking the same thing but I was not understanding what the objection was to what he suggested. Jan 4, 2023 at 18:28
  • 1
    Looking it over, I like @KirkSaunders approach on the tables (not the JSON), it's pretty much what I'd do. Use the relational structures of this relational database to build things out. As soon as you start typing '1' and '2' and '3' in column names, at that exact moment, you should know that it's time to add a table so you can manage the data through rows. Jan 5, 2023 at 14:25

2 Answers 2

4

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.

2
  • Thank you for your advice, but it doesn't seem to fit my specific needs. A third table must contain a country ddl value that is specific to each field (column in the db table), thus if there are 40 fields and 10 countries, the third table must contain a total of 400 records or rows (ID columns duplicate for 10 countries). I have updated the question with more info. could you please check again? Jan 4, 2023 at 13:27
  • If you want to store a value for all 40 fields for all 10 countries, then yes, you will have 400 records in all. If you have no values for any of the countries, you would have zero records. Having those "numbered" fields makes me uncomfortable with this design - repeated fields should [almost always] be normalised out into rows with a way to identify each one. Either given those "ColumnField*" columns meaningful names or normalise things, with each new record having a "name" field as well as the foreign keys of the related fields / countries, plus the actual value(s).
    – Phill W.
    Jan 4, 2023 at 15:32
3

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 1/2 Re-formatted

Table 3 is then re-formatted into something like (where JobNumber, Country, FieldName is unique):

Table 3 Re-formatted

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).

Table 1/2 Reformatted and Normalized

And a Table 3 that look something like (where JobNumber, CountryId, FieldId is unique):

Table 3 - Re-formatted and Normalized - Part 1

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.:

Table 3 - Reformatted and Normalized - Part 2

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:

JSON Blobs

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.