On Wed, Jul 15, 2015 at 11:15 AM, David G. Johnston < david.g.johns...@gmail.com> wrote:
> > Yes, you are trying to choose between a bunch of one-to-one (optional) > relationships versus adding additional columns to a table all of which can > be null. > > I'd argue that neither option is "normal" (in the DB normalization sense). > > CREATE TABLE meal (meal_id bigserial) > CREATE TABLE meal_event_type (meal_event_id bigserial) > CREATE TABLE meal_event (meal_id bigint, meal_event_id bigint, occurred_at > timestamptz) > > So now the decision is one of how to denormalize. materialzed views and > two ways to do so. The specific solution would depend in part on the final > application queries that you need to write. > > If you do want to model the de-normalized form, which I would likely be > tempted to do given a fixed set of "events" that do not require additional > related attributes, would be to place the few event timestamps on the main > table and UPDATE them to non-null. > > In the normal form you will likely find partial indexes to be quite useful. > > David J. > > > Thanks David, my example was a big simplification, but I appreciate your guidance. The different event types have differing amounts of related data. Query speed on this schema is not important, it's really the write speed that matters. So I was just wondering given the INSERT or UPDATE approach (with no indexed data being changed) if one is likely to be substantially faster than the other.
