Re: design question

"JOG" <jog_at_cs.nott.ac.uk> wrote in message news:aeaa583e-a70c-4fb5-9e92-40940d83a40a_at_e1g2000pra.googlegroups.com... On Nov 5, 1:17 pm, "Walter Mitty" <wami..._at_verizon.net> wrote:
> "Ed Prochak" <edproc..._at_gmail.com> wrote in message
>
> news:98c0f37a-3594-4158-9e7a-c525b9e50df7_at_p10g2000prf.googlegroups.com...
> On Oct 28, 3:06 am, robu..._at_gmail.com wrote:
>
> > Walter Mitty wrote:
> > > <robu..._at_gmail.com> 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. Received on Fri Nov 07 2008 - 21:45:07 CET