should entities in a database be named using singular or plural

#11

The paper also states that the surrogate key is created and maintained
by the system, not the user nor is it ever seen by the user. Think of
idexing, perfect hashing, etc. as ways to locate physical data.
Unfortunately, auto-incremenation in many SQLs is not a surrogate key
at all -- it is an exposed phsyical locator (physical row number,
record count or whatever).

#12

On Aug 19, 8:01*am, "Walter Mitty" <wami... (AT) verizon (DOT) net> wrote:

Quote:

"Lennart" <erik.lennart.jons... (AT) gmail (DOT) com> wrote in message

news:ac943c75-81d8-46ac-8aea-e1eb527ad304 (AT) c1g2000yqi (DOT) googlegroups.com...
On 14 Aug, 14:48, Terrence Brannon <metap... (AT) gmail (DOT) com> wrote:
[...]

Microsoft seems to think there is
not:http://msdn.microsoft.com/en-us/libr...10(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 tobe
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 bean
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 constrainton 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

#13

"Ed Prochak" <edprochak (AT) gmail (DOT) com> wrote

On Aug 19, 8:01 am, "Walter Mitty" <wami... (AT) verizon (DOT) net> wrote:

Quote:

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

Right. Nothing I've read or cited suggests otherwise. Certainly not the
paper from which I cited the paragraph.

My intent was to make a contrast between Codd's idea of the relational model
and the design patterns advocated in the "Oslo paper".

Codd's idea of the relational model did evolve over time. But, to my
knowledge, none of that evolution affects the use of ID keys.

Quote:

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.

What I said is that analysis is difficult. A lot of people confuse data
analysis with database design. Database design is also somewhat difficult,
but it can be learned. Someone once said that it's easier to understand 100
tables than 100,000 lines of code. It's also easier to design 100 tables
than to design the object model that will eventually result in 100,000 line
of code. Some learners settle for easy rules like the Oslo pattern instead
of learning harder rules that give better results.

And I agree that the universal ID key can be more trouble than it's worth.

Today's situation is complicated by the fact that many developers are
developing products for sale to some presumed future market of buyers.
There is no available existing body of data, and there are no subject matter
experts to interview, in order to find out how they really use the data. So
the developer doesn't do analysis the way the classical database designer
would have. Instead of talking to people who understand the data, he
engages in "imagineering", and invents his own understanding of the data,
along with inventing the database design.

Database design involves invention. Data analysis involves discovery. They
ain't the same.

#14

Quote:

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

You cabn see a lot of Codd's changes over time. PRIMARY KEY was based
on the keys needed for a tape file merge or other sequential file to
work; later, he realizes that "no key is more equal" than others, to
make fun of Orwell. In Relational Model #2, he has two kinds of
NULLs. Etc. He became more abstract over time, as he realized the
assumptions that we were carrying into RDBMS thinking.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

should entities in a database be named using singular or plural

comp.databases comp.databases

Re: should entities in a database be named using singular or plural - 08-19-2009 , 01:44 PM

Re: should entities in a database be named using singular or plural - 08-20-2009 , 10:48 PM

Re: should entities in a database be named using singular or plural - 08-21-2009 , 07:32 AM

Re: should entities in a database be named using singular or plural - 08-21-2009 , 10:03 AM