0

I want to build a table to store policies, and policies in some cases have a field named "certificate_number" but let's say from 100 policies only 20 will have this field.

So which way is more efficient?

1.-Adding an nullable field to the policies table and make it null if there is no need for certificate_number

2.-Creating a separated table something like certificate_number_policy that references a policy with a foreign key and has a field certificate_number

3.-Third option?

which way should I pick? the first option is obviously simpler but I will have tons of null fields, and the second one leaves not null fields but adds complexity...

Anibal Cardozo
  • 479
  • 1
  • 10
  • 21
  • 1
    It depends on how you want to handle it. There’s no “best” not “efficient” option unless the requirements are explained very specifically. Best how? Efficient based on storage used? Query speed? Memory usage? Something else? Is there so much data that this will matter much? – Sami Kuhmonen Nov 13 '19 at 19:35

2 Answers2

0

Definitely 2 You can dynamically edit the certificate_number list Allowing the user to select only the "certificate_number" list to prevent errors. Google translator

sthruska
  • 1
  • 1
  • 1
0

Honestly, having the same problem as you , I had created another linked table with fields : 

primary table link / policies_name / values

So you add all the policies you want with their values.

This way you can have 5 policies for PrimaryPolicies ID #1 , and 3 policies for PrimaryPolicies ID #2 And you can add or not empty policies.

     PrimarytableLinkID = 1 / Policies_name = 'certificate_number' / values = '1xxx1'
     PrimarytableLinkID = 1 / Policies_name = 'other policies' / values = 'yyyyy'
baronming
  • 210
  • 1
  • 3
  • 19