Re: Newbie question about db normalization theory: redundant keys OK?
Date: Sat, 15 Dec 2007 05:17:09 GMT
Message-ID: <prJ8j.53582$eY.44749_at_newssvr13.news.prodigy.net>
"David Portas" <REMOVE_BEFORE_REPLYING_dportas_at_acm.org> wrote in message news:1065f96a-1f8e-4fc7-b1cc-53137ebd8b8f_at_s8g2000prg.googlegroups.com...
> On 14 Dec, 17:51, "Tony Rogerson" <tonyroger..._at_torver.net> wrote:
>> First of all, let's go back to your statement about just store the
>> history -
>> can you please show how you would do that with celko schema and still be
>> able to reach the rows as per my examples?
>>
>> > precisely: "The real world gets in the way" tells us nothing about why
>> > it would be a problem to update email address X to become email
>> > address Y. I just don't see what you are getting at.
>>
>> Here's another...
>>
>> create table blah (
>> emiladdress varchar(?) not null unique,
>> who varchar(100) not null
>> )
>>
>> In the year 2006 I have an email address called
>> tonyroger..._at_sqlserver.eu.com everybody sends email to that address.
>>
>> insert blah values( 'tonyroger..._at_sqlserver.eu.com' )
>>
>> On 1st Jan 2007 I change the email address to tonyroger..._at_torver.net I
>> suddenly stop receiving emails because everybody is still emailing
>> tonyroger..._at_sqlserver.eu.com.
>>
>> update blah set emailaddress = 'tonyroger..._at_torver.net' where
>> emailaddress
>> = 'tonyroger..._at_sqlserver.eu.com'
>>
>> How do all the applications disconnected from the database now reach the
>> record? They can't - the natural key has changed.
>>
> > You are saying that this is a distributed database but that it lacks a > mechanism for accurately propagating changes out to all its nodes? > Well in my view such a DBMS would be broken. It surely violates Codd's > principle of "Distribution Independence". Let's follow your example to > its conclusion though. The solution is to replace whatever copy of the > Blah relation exists in the application with the new Blah relation > that superceded it. Now all emails reach the correct address and there > is no problem that requires a different key. >
Forgive me for butting in, David, but where did you come up with the idea that it is a distributed database? What have disconnected applications--that is, applications that use something akin to disconnected ADO recordsets or ADO.NET datasets--to do with distributed databases?
The question is: for how long is the data that was just read out of the database considered to be valid? Until the next update? Or is it stale as soon as its read? Does it have something to do with transaction control or locking? If several updates occur between the reading of one piece of information and the reading of another, how can you be sure that any answer that involves both pieces of information is correct? How can you be sure that you haven't read the same information twice? If you use an artificial key and a timestamp (or rowversion), then there can be no doubt as to whether or not the information in question changed between the first reading and the second.
> -- > David Portas > > >Received on Sat Dec 15 2007 - 06:17:09 CET