Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Normalization, Natural Keys, Surrogate Keys

Re: Normalization, Natural Keys, Surrogate Keys

From: Ed Prochak <edprochak_at_magicinterface.com>
Date: Tue, 04 Jun 2002 11:30:43 GMT
Message-ID: <3CFCD14D.3EBA4069@magicinterface.com>

Thank you Rich. I could not have said it better.

Rich Dillon wrote:
>
> > How have I turned it into a network model? First of all, I don't
> > believe there is that much difference between the relational and
> > network models. From all I can remember they both have 1-M and 1-1
> > relationships. If I recall the only real differnce is that in a
> > relational DBMS I can create my own keys whereas I cannot in a
> > heirarchical. Also a relational DBMS cannot create a network database
> > and vice-versa. Maybe you could explain it to me.
>
> One of the most fundimental principles of the Relational Model is that *all*
> of the infomation in a database is represented in *only* one way: as
> explicit values in relations.
>
> CREATE TABLE transactions (
> trans_no INT NOT NULL PRIMARY KEY,
> trans_date DATETIME NOT NULL,
> acct_no INT NOT NULL REFERENCES accounts,
> ....);
>
> In the Relational Model, there is only one data structure (the relation)
> which represents a set of propositions about our knowledge regarding some
> aspect of the world: "Transaction <trans_no> debited the account <acct_no>
> by ...". There are no unneccesary structures, no pointers, no dependencies
> in the logical model on physical storage details. In contrast, the Network
> Model has at least one unnecessary structure -- a seperate link structure
> pointing from one row in one table to one row in another.
>
> Toward the beginning of this amazingly long thread I used a simple example
> to demonstrate that using surrogate keys alone is equivalent to little more
> than saying "this is row 1, this is row 2" etc. So, given this meaning, the
> appearance of the same surrogate key in a referencing table says simply
> "this row points to row 1 in the other table". The table definition above
> says instead "transaction <trans_no> references account <acct_no>". There's
> an important difference in thinking. One statement talks only about linking
> between data structures (and we shouldn't be talking about those), the other
> deals entirely with attributes of the real world entity in which we're
> interested.
>
> And the confusion is all too common. Someone else was going on in another
> discussion about "creating rows and linking data into them" as if rows were
> themselves objects of interest -- perhaps an OO programmer equating rows to
> objects (instances of a class) with their own intrinsic identities and, oh
> yeah, data too.
>
> Ed's comments could be taken, perhaps, as a bit of hyperbole. You can't
> create a true network model in SQL (though SQL '99 moves us back that way by
> trashing the language with pointers). However, surrogate keys are commonly
> employed by developers with a network model mindset. They're thinking of
> row identifiers and pointers in their mind, "this row points to that row".
> And if they see linking tables together as the whole game then they commonly
> neglect to declare the keys which would protect their data.

-- 
Edward J. Prochak   --- Magic Interface, Ltd.
Ofc: 440-498-3700
on the web at       --- http://www.magicinterface.com
email: ed.prochak_at_magicinterface.com
Received on Tue Jun 04 2002 - 06:30:43 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US