Re: Whither 1:1? - Mailing list pgsql-general
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?
I make considerable use of one-to-one tables. There isn't any inherent absolute downside to doing this that I can think of (potentially more joins are neither inherent nor absolute) though the specific benefits or concerns rely upon the specific model being implemented.
The two ways I've done this is "static vs dynamic data" split and, basically, "attribute grouping". This is one that can avoid storing nulls though in my particular usage I still have them since I'm basically implementing a key-value json document and so have a record for the ID in every table even if no data is present. The advantage is reducing model complexity by adding meta-data to all of the fields contained fields.
David J.
pgsql-general by date: