Need help designing database, Create a table with additional columns/fields in the table, or create a new table for each different type?

All we need is an easy explanation of the problem, so here it is.

So the tables that I have are Certificates, Approvers, CertificatesApprovers, Categories, Approvees, Status, AdditionalFields, AdditionalFieldsValues.

Many Certificates to Many Approvers relationship.

1 Category to Many Certificates relationship.

1 Category to Many AdditionalFields relationship

1 AdditionalFields to Many AdditionalFieldsValues relationship

If there are different types of Certificates which will have different fields/columns, should I create a different unique table for each type of Certificate or just add the additional fields/columns in the Certificates table (but some of the entries will be blank)?

For example, a MedicalCertificate will have a field Specialization whereas a DrillCertificate will have a field DrillType.

Certificates Table

ID CategoryID ApproveeID ValidFrom ValidUntil StatusID Specialization DrillType
1 2 4 1/4/2021 2/4/2021 1 Oncology
2 1 5 1/4/2021 2/4/2021 1 Traditional

OR

MedicalCertificates Table

ID ApproveeID ValidFrom ValidUntil StatusID Specialization
1 4 1/4/2021 2/4/2021 1 Oncology
2 5 1/4/2021 2/4/2021 1 Haemotology

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

I’m more of a fan of your second option because it’s more normalized than the first option, but even better for normalization would be to have one Certificates table with the common fields in it ID, ApproveeID, ValidFrom, ValidUntil, StatusID and then a generic auxillary table to store the uncommon fields in.

For example, the auxillary table could be called CertificatesAdditionalInfo with fields like ID, CertifcateID, Info1Name, Info1Value, Info2Name, Info2Value, etc. The info fields would be nullable so that they’re flexible.

Implementing it this way means you only need one table for the main data for Certifcates, which will lead to less data duplication, better long term maintainability, and likely improved performance. Also please pick better table and column names (though the column names should be generic) than me, as I suck at naming things. 🙂

Method 2

1:many — Just an id in one of the tables to link to the other.

many:many — Need a table to link them. It will have (probably) exactly 2 columns, namely the two ids.

key-value pairs — read about EAV and its evils and benefits.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply