3

Perhaps similar questions have been asked, but this is re having null columns.

I am too inexperienced to determine what would be the sounder way to approach the design of this database. Is it better to have 5 or 6 additional columns on the one table that could reference IDs of other tables, or have 5 or 6 extra tables that provide a link if there is a need for them.

My inclination is that fewer null entries are better, but then joining the tables when accessing the database could have a higher cost?

6
  • 2
    In practice the difference in cost between both approach is too small to be troubled of it. Each approach has another benefits and disadvantages that become reasonable depending on the specific project.
    – Kondybas
    Oct 29 '18 at 14:49
  • 1
    It depends. Suggest you implement one way, but plan on reassessing the situation in a month or so. Then switch to the other way. This can be done with a small number of SQL statements; it will be good practice.
    – Rick James
    Oct 29 '18 at 17:06
  • 1
    Your question may have an answer here Oct 29 '18 at 21:16
  • 1
    @Kondybas Have you got some information or a link to what these benefits and disadvantages, in case there are things I've not considered?
    – MikeyB
    Oct 30 '18 at 9:07
  • 1
    @MikeyB Splitted (normalized) design gives a better comprehension of the data nature/stucture and better conforms the relational theory. But this approach require more JOINs that can be a significant overhead. If the sparsed data is massively searched on the regular basis it would be better to combine it with the main data. If that search is performed only occasionally then the lightweight main table joined with nonsparsed referral table is preferrable.
    – Kondybas
    Oct 30 '18 at 9:41
-2

Allow null is a better approach. It gives us significant performance benefits with significant effort shortage to accomplish the task. A separate table will cost more JOIN. To query such a data is/can be certainly slower, than to query single table

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.