Olivier Gautherot <olivier@gautherot.net> writes:
> On Fri, Jun 1, 2018 at 12:52 PM, Guyren Howe <guyren@gmail.com> wrote:
>
>> It’s come to my attention that what seems an obvious and useful database
>> design pattern — 1:1 relations between tables by having a shared primary
>> key — is hardly discussed or used.
>>
>> It would seem to be a very simple pattern, and useful to avoid storing
>> nulls or for groups of fields that tend to be used together.
>>
>> Thoughts? Is there some downside I can’t see?
>>
>
> You will get a benefit in terms of space only if the optional fields in the
> second table exist in a reduced number of instances - and the second table
> is significantly wider. This can make a difference on big tables but this
> gain may be offset by the cost of the join. In this perspective, I don't
> think that there is a clear benefit or drawback: it should be evaluated on
> a case-by-case basis.
>
Well said. Like many database design decisions, there are more
guidelines than rules. Nearly all decisions have pros and
cons. Therefore, you need to assess on a per project basis. In addition
to the structure of data (size, null frequency etc), you also need to
consider how the data is used. It is also important to consider who will
be using the data, how they will access it and what level of
sophistication/understanding they have. The clarity of your data model
is also important as future decisions may be made by others and the
better they understand the design/model, the higher the likelihood the
system will evolve in a sane and maintainable manner.
There other point to remember is that all designs often have an element
of surprise - you make a decision under an assumption which turns out
not to hold due to variables you didn't consider or don't have control
over. Sometimes there are alternatives which may actually perform better
because they are optimised within the system - for example, some of the
benefits for this approach could be realised using partitions.
I have used this technique, but from memory, this was done as the system
evolved and we found there was a benefit from having a smaller 'main'
table. It isn't a design decision I recall adopting during an initial
modelling of the system, but it may be something to consider once you
find a performance problem (along with other options) which needs to be
addressed.
I'm not aware of any guideline or school of thought which rules out this
as an option. Possibly the reason it appears to be used infrequently is
because it doesn't realise the benefits you might expect or is simply
not an problem in a majority of use cases.
Tim
--
Tim Cross
