Re: Normalization, Natural Keys, Surrogate Keys

> 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. Received on Sat Jun 01 2002 - 02:25:49 CDT