Need help designing database, Create a table with additional columns/fields in the table, or create a new table for each different type? 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
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.