design question

"Ed Prochak" wrote in message

On Oct 28, 3:06 am, wrote:
> Walter Mitty wrote:
> > wrote in message

> > Are you sure you don't mean "primary keys made up by composing several
> > foreign keys"? Just a guess on my part, since I don't really know what
> > you
> > mean.
>
> Sorry, I was in hurry so I was not very clear. I mean some people
> prefer to use surrogate (primary) keys instead of natural composite
> keys and then use foreign keys to surrogates just for making joins
> "faster". A bad idea in my opinion...

>I favor your view. There is a time and place for surrogates, but too
>many jump to using ID columns as the PK right away.

I also agree. I only use ID columns for "entity tables" not "relationship
tables". And I only use them when there are no reliable natural keys
available.
 >> Stay informed about: design question 

On Nov 5, 1:17 pm, "Walter Mitty" wrote:
> "Ed Prochak" wrote in message
>
>
> On Oct 28, 3:06 am, wrote:
>
> > Walter Mitty wrote:
> > > wrote in message
> > > Are you sure you don't mean "primary keys made up by composing several
> > > foreign keys"? Just a guess on my part, since I don't really know what
> > > you
> > > mean.
>
> > Sorry, I was in hurry so I was not very clear. I mean some people
> > prefer to use surrogate (primary) keys instead of natural composite
> > keys and then use foreign keys to surrogates just for making joins
> > "faster". A bad idea in my opinion...
> >I favor your view. There is a time and place for surrogates, but too
> >many jump to using ID columns as the PK right away.
>
> I also agree.  I only use ID columns for "entity tables" not "relationship
> tables".  And I only use them when there are no reliable natural keys
> available.

So aptly and simply put. Why this isn't at the top, printed in bold,
of texts on the subject I have no idea.
 >> Stay informed about: design question 

"JOG" wrote in message

On Nov 5, 1:17 pm, "Walter Mitty" wrote:
> "Ed Prochak" wrote in message
>
>
> On Oct 28, 3:06 am, wrote:
>
> > Walter Mitty wrote:
> > > wrote in message
> > > Are you sure you don't mean "primary keys made up by composing several
> > > foreign keys"? Just a guess on my part, since I don't really know what
> > > you
> > > mean.
>
> > Sorry, I was in hurry so I was not very clear. I mean some people
> > prefer to use surrogate (primary) keys instead of natural composite
> > keys and then use foreign keys to surrogates just for making joins
> > "faster". A bad idea in my opinion...
> >I favor your view. There is a time and place for surrogates, but too
> >many jump to using ID columns as the PK right away.
>
> I also agree. I only use ID columns for "entity tables" not "relationship
> tables". And I only use them when there are no reliable natural keys
> available.

>So aptly and simply put. Why this isn't at the top, printed in bold,
>of texts on the subject I have no idea.

I have no idea. Back in the early eighties, when I first learned database
concepts, the best materials were presented in lectures, and backed up by
advance notes given out by the lecturer. The best presentation I saw was
organized into three stages:

The first stage was data analysis resulting in a conceptual data model. The
conceptual data model was ER modeling. I'll admit that, at first, the ER
model looked like a pale imitation of the relational model to me. It wasn't
until years later that I began to truly appreciate the simplicity and power
of the ER model. The ER model is more important for what it DOESN'T say
than for what it does say. That helps prevent design decisions from
creeping into the analysis model.

The second stage was logical database design, resulting in a logical data
model. This model was specific to relational databases, but not specific to
which particular DBMS product was intended for implementation. At that
time, I never ran into the religious warfare about the difference between
SQL modeling and relational modeling that I've since seen in this newsgroup.
While there are some very real divergencies between SQL and the RDM, most
of those differences are unimportant to the task of designing and building
something real.

The logical model can re described in terms of relations, attributes, and
constraints, although I've taken to using the terminology of tables, columns
and constraints. Index design was treated as a kind of transition between
logical modeling and physical modeling. And yes, I know all about the fact
that indexes are unnecessary to describe a complete logical model. So those
of you who are thinking of reproving me for heresy can just skip the
response this time. It's archived in oodles of c.d.t. discussions over in
Google groups anyway.

The third stage was physical database design, resulting in a physical data
model. This was DBMS specific, as well as taking into account data
volumes, platform resources, responses time requirements, interface
languages and the like. The ususal mode of presenting the physical model
was CREATE scripts. Again, there was a real absence of religion, and a real
emphasis on making simple but sound decisions early on.

This stuff was simple and sound. It respected theory, but took an immensely
practical view of design.



Since that time, I've seen presentations that one the one hand take a
religious view of "right design" versus "wrong design" in a world where
there is typically a large number of satsifactory designs, and several
dimensions on which to measure "goodness" of design. One the other hand,
I've seen lots of presentations that militate for design choices that I'm
going to dismiss as just plain wrong, at the expense of being seen as
religious myself. The idea that every table should have a first column
named ID that contains an integer is one such wrong headed idea. But if you
look at such things as the "Northwind" database that comes with MS Access,
that's exactly the pattern you're going to see (unless they've changed it
for the newest version).

I've got more to say, but I've rambled enough already.
 >> Stay informed about: design question