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?

Anthony Genovese
  • 2,063
  • 3
  • 20
  • 34
MikeyB
  • 131
  • 3
  • 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 – Walter Mitty 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 09: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 09:41
  • Does this answer your question? Is it a bad practice to have several mutually exclusive one-to-one relationships? – Michael Green Aug 05 '20 at 07:46

1 Answers1

-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

Mahesh Bhosale
  • 1