Re: Enforce primary key on every table during dev?

On Thu, Mar 1, 2018 at 11:43 AM, Alban Hertroys <haramrae(at)gmail(dot)com> wrote:

> On 1 March 2018 at 17:22, Steven Lembark <lembark(at)wrkhors(dot)com> wrote:
> >
> >> On 03/01/2018 02:20 AM, Alban Hertroys wrote:
> >> [snip]
> >> > Not to mention that not all types of tables necessarily have
> >> > suitable candidates for a primary key. You could add a surrogate
> >> > key based on a serial type, but in such cases that may not serve
> >> > any purpose other than to have some arbitrary primary key.
> >> >
> >> > An example of such tables is a monetary transaction table that
> >> > contains records for deposits and withdrawals to accounts.
>
> (...)
>
> > Start with Date's notion that a database exists to correclty represent
> > data about the real world. Storing un-identified data breaks this
> > since we have no idea what the data means or have any good way of
> > getting it back out. Net result is that any workable relational
> > database will have at least one candidate key for any table in it.
>
> (...)
>
> > If you have a design with un-identified data it means that you havn't
> > normalized it properly: something is missing from the table with
> > un-identifiable rows.
>
> While that holds true for a relational model, in reporting for
> example, it is common practice to denormalize data without a
> requirement to be able to identify a single record. The use case for
> such tables is providing quick aggregates on the data. Often this
> deals with derived data. It's not that uncommon to not have a primary
> or even a uniquely identifiable key on such tables.
>
> I do not disagree that having a primary key on a table is a bad thing,
> but I do disagree that a primary key is a requirement for all tables.
>
> More generally: For every rule there are exceptions. Even for this one.
> --
> If you can't see the forest for the trees,
> Cut the trees and you'll see there is no forest.
>
>

*> it is common practice to denormalize data without a>requirement to be
able to identify a single record *

*You may perceive that to be "common practice", but in reality it is not,
and in fact a bad one. As was previously stated, PosgreSQL is a
_relational_ database,*
*and breaking that premise will eventually land you in very big trouble.
There is no solid reason not to a primary key for every table.*

--
*Melvin Davidson*
*Maj. Database & Exploration Specialist*
*Universe Exploration Command – UXC*
Employment by invitation only!