Re: Insert vs Update

On Wed, Jul 15, 2015 at 11:15 AM, David G. Johnston <
david(dot)g(dot)johnston(at)gmail(dot)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_even​t_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.