On Thu, Mar 1, 2018 at 11:43 AM, Alban Hertroys <haram...@gmail.com> wrote:
> On 1 March 2018 at 17:22, Steven Lembark <lemb...@wrkhors.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!
