Re: design question


"JOG" <jog@xxxxxxxxxxxxx> wrote in message
news:aeaa583e-a70c-4fb5-9e92-40940d83a40a@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
On Nov 5, 1:17 pm, "Walter Mitty" <wami...@xxxxxxxxxxx> wrote:
"Ed Prochak" <edproc...@xxxxxxxxx> wrote in message

news:98c0f37a-3594-4158-9e7a-c525b9e50df7@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
On Oct 28, 3:06 am, robu...@xxxxxxxxx wrote:

Walter Mitty wrote:
<robu...@xxxxxxxxx> 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.



.

Relevant Pages

  • Re: Clues on modeling a really simple concept
    ... clue about what kind of design will give you the most fun. ... object modeling, but that's really a subject for a different discussion. ... doesn't tell  you how to design the database. ... For the logical model, ...
    (comp.databases.theory)
  • Re: [OT] Normalization
    ... >> primary keys on every database table. ... >> changed the database design for those situations was to provide a better ...
    (comp.lang.php)
  • Re: Clues on modeling a really simple concept
    ... Im modeling this software for fun. ... I had no idea that the purpose of the project was just for you to have fun. ... clue about what kind of design will give you the most fun. ... doesn't tell you how to design the database. ...
    (comp.databases.theory)
  • Re: data modeling software
    ... I'm just not overly enthralled with the relationships window in Access. ... Some of the other tools make modeling a little bit easier, as you design the ... database) at a time. ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Relation Schemata vs. Relation Variables
    ... Identity beyond that provided by the identifying keys is a nonsense. ... design a system whether a key that appears to be very stable will remain ... I was called in to fix a problem at a company where management ... During that period the database became corrupt because it allowed changes ...
    (comp.databases.theory)