Re: should entities in a database be named using singular or plural

On Aug 19, 8:01 am, "Walter Mitty" <wami...@xxxxxxxxxxx> wrote:
"Lennart" <erik.lennart.jons...@xxxxxxxxx> wrote in message

news:ac943c75-81d8-46ac-8aea-e1eb527ad304@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
On 14 Aug, 14:48, Terrence Brannon <metap...@xxxxxxxxx> wrote:
[...]



Microsoft seems to think there is
not:http://msdn.microsoft.com/en-us/library/dd129510(VS.85).aspx

A page full of ... surprises

* Append the Word Table to the End of Table Names
* Create a Primary Key Named Id

perhaps there is more, I did not read any further ...

/Lennart

It's interesting to compare the rationale for creating a primary key named
ID with the relational view of data described by Codd in 1970.

Here's a note from the paper:

"An arbitrary primary key, such as Id, is referred to as a surrogate key.
The alternative would be to use a natural key that is part of the data
itself (such as a government-issued identification number). Surrogate keys
are preferred, because it is sometimes difficult to accurately find a column
or combination of columns that will maintain uniqueness. In addition,
natural keys can be much larger than surrogate keys, especially if the
natural keys are text based or composed of multiple columns. Large keys
increase I/O demands on the table and its indexes, decreasing database
throughput."

And here's an excerpt from the 1970 paper introducing the relational model
of data for use with databases.

"The relational view (or model) of data described in Section 1 appears to be
superior in several respects to the
graph or network model [3,4] presently in vogue for noninferential systems.
It provides a means of describing data
with its natural structure only-that is, without superimposing any
additional structure for machine representation
purposes."

The automatic assignment of an unnatural key called ID in every table raises
the question of whether the Oslo design is really following the relational
model of data, or whether ID is not being used in a way that mimics the
record location (pointer) that would have been used in a graph or network
model.  It's also instructive to note that, in the paper, the ID is
described as identifying the row in which it resides.  Natural keys might be
used to uniquely specify a row, but they identify the subject matter item
that the data in the row represents.  This subject matter item would be an
instance of an entity or an instance of a relationship among entities if you
follow the earlier comments in the paper.

It is, as the paper states, sometimes difficult to accurately find a column
or combination of column that will maintain uniqueness.  Yes, it is
difficult.  Data analysis is difficult.  Building a database around data
that is inadeqately understood is also problematic, but at a different
level.  If an ID PK will prevent duplicate rows, but not prevent duplicate
table entries for a single instance of an entity, you've defeated one of the
major reasons for having a primary key.  If you add a unique constraint on a
natural key to the PK constraint on ID, you lose some of the perfromance
advantages claimed for the use of ID.

I think the comments reflects Codd's evolving development of the
relational model. So I do not think he really advocated use of ID.

And I especially agree with the last paragraph. Database design is
difficult. It is not simply a question of throwing a few tables with
ID columns together. And an ID key can be more trouble than it is
worth.

Ed

.

Relevant Pages

  • Re: How should I generate a primary key?
    ... Distributed systems can be seen as a slightly more complex database setup ... the primary key cannot be changed. ... change whatever your natural key is and still have a link between the ... Imagine you are writing a system for the DVLA (if you're not from the U.K. ...
    (comp.databases)
  • Re: should entities in a database be named using singular or plural
    ... It's interesting to compare the rationale for creating a primary key named ... or combination of columns that will maintain uniqueness. ... natural keys are text based or composed of multiple columns. ... Building a database around data ...
    (comp.databases)
  • Re: help with interview question
    ... So maybe he had in mind structure like B*tree (see specification of database ... indexes without constraint on uniqueness unless you do not add number to the ... primary key - for me searching telephone book is giving answer (or many ...
    (comp.theory)
  • Re: Table Design Question
    ... > primary key at the logical level. ... > model which is being represented in a database. ... I'm claiming that there really isn't a good natural key that you ... generic key is not only acceptable, ...
    (microsoft.public.sqlserver.programming)
  • Re: How should I generate a primary key?
    ... change a primary key takes x + y. ... To change a non-primary key takes x. ... Using a natural key often means one less join. ... In any database large enough to start worrying about cascading updates ...
    (comp.databases)
Loading