0

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
0

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

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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