Re: Normalization, Natural Keys, Surrogate Keys
Date: Sat, 1 Jun 2002 00:25:49 -0700
Message-ID: <#k8rb1TCCHA.1548_at_tkmsftngp02>
> How have I turned it into a network model? First of all, I don't
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,
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
> 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.
trans_date DATETIME NOT NULL,
acct_no INT NOT NULL REFERENCES accounts,
....);