Re: Identity modelling

Gene Wirchenko wrote:

> On Tue, 30 Aug 2005 13:08:28 +0300, "x" <x_at_not-exists.org> wrote:
> 
> 

> 
> 
>      It is a key since it does not specify the location.  In a way,
> DNS is concerned with translating this key into a pointer.
> ...

Apologies if I'm taking David's original question above out of context, but 'out of context' seems important in this case. As far as the RM is concerned, my understanding is that within the RM something (ie. attributes) is a key only within a relation. It's a set of attributes with some constraint on their values that allows other single-valued attributes to be dependent on the key values. There are deeper reasons for it, but that's the only essential one I know of.

Some people say a key is an "address" because keys have been described as the RM's "addressing mechanism". It doesn't follow that a physical storage address is at all the same thing. For example, outside the RM, how could a physical address possibly point to a tuple in a relation?

As far as the RM is concerned, once you take it out of the database context, it can be anything one might want it to be even if it doesn't make sense. You could call it Margaret Thatcher or a kumkwat and the RM wouldn't care. This is okay even if it doesn't make sense. It must be, witness all the applications that don't make sense.

There was some talk recently to the effect, if I read it right, that a generated IDENTITY (perhaps the capitals have some special meaning to some particular product) somehow pollutes or entangles or locks-in a logical database. Not sure whether this particular IDENTITY was intended to be a value that is merely generated somehow or if it refers to, say, a storage location of some physical machine.

I'd say whether it is one or the other doesn't matter at all to the RM. It may matter to some application that happens to be mapping memory but that's neither here nor there for the RM. There's nothing I know of in the RM that forbids a certain value. If some user or some program inserts it, all the database wants to know are the internal rules it should follow in deciding whether to allow the insert. If it is a key, then one mandatory rule will be that no tuple with the same value for those attributes can be the same. There might be other rules too and a designer might write some kind of trigger or procedure that goes outside the database to see if it's a real address. That sounds like application to me.

Same thing goes for generated keys. If they happen to be sequential, the RM won't care, as long as they are 'unique'. Some application could depend on sequence but it would have to guarantee the sequence itself. Good argument for user domains. Guaranteeing sequence without gaps is a bigger problem than many people realize in fact I believe even though I can't prove it except anecdotally that there are always situations when it won't work. So an application would have to do this itself and make whatever conditions it need to stick, stick. It might happen to use some DBMS features to make this happen such as locking support, but I don't think there is any RM feature it could use or at least any that would be the only way to do it.

The arguments about IDENTITY or anything else for that matter, being problematic to a 'relational' database because they are physical and not logical *outside* the database are apples and oranges and straw at the same time. Just noise for consultants and various writers to make when they can't think what to say next. Confusing silence with stupidity.

I detect a consultant's trick in the argument that the database must contain only the 'logical' values and not 'physical' ones, where customers or readers might be confused by all the red herrings thrown up   into thinking that if values are only "logical" then the corollary being that the database that has been designed for them must be logical too, a kind of sine qua non for their application being logical to boot.   This is complete BS.

There's also nothing in the RM, at least what I've read about it, that prefers generated keys over 'natural' keys. A generated key might be a tell-tale that some designer wasn't capable of considering the uses of natural keys in future applications and simply copped out. But it could mean the opposite as well - that the designer was fully aware of all intentions for the database and was explicitly ruling out natural keys.

There's at least one other time when a generated key could help, for example when you've screwed up the design and people have put lots of data into the db. Then you could, conceivably, at least append a key, take some projections and join / union them into the tables you should have designed in the first place.

What a user thinks a key means is something the RM isn't really concerned with. It's up to the designer to tell them how it should be used, ie. what it means as far as the user doing the user's job is concerned. The designer might tell one set of users, say the Human Remains people, that there are two sets of departments, one that the Payroll users see in the view defined for them and another one that the HR people can see as well. Just two attributes in the DEPT table, only the Payroll people can't see one of them. Why would you do this? Well, the HR people could update one row and move everybody in one department to another. Only the HR people would have to remember that the two attributes had different meanings. Contrived example, I'll admit, but it does show why users have to be educated by the designer.

Ah, that feels better,
thanks,
p Received on Tue Aug 30 2005 - 21:12:43 CEST