Re: GUID as primary key

From: Andreas Mosmann <keineemails_at_gmx.de>
Date: Mon, 20 Jun 2005 18:38:08 +0200
Message-ID: <1119285488.55@user.newsoffice.de>

Billy schrieb am 20.06.2005 in
<1119276236.214749.25130_at_g43g2000cwa.googlegroups.com>:

> Andreas Mosmann wrote:

>> There are some reasons for not using sequences
>> - a sequence looses the effect if only 1 row is inserted with a number
>> higher than AnySequenceName.CurrVal, so you have to fix this in an
>> application.

> The same possibility exist with a GUID. A manual insert, bypassing the
> "column generator" for that column (e.g. the trigger/function
> calculating the GUID or SEQUENCE for the column) will cause problem
> either way. And you will have to fix the data - either way.

not exactly the same, because it is not probably, that you can generate a GUID incidently, it would be easier to call the procedure for it (32 characters with special signs at the correct positions - you must be stupid to build it on your own). The other way is to copy a GUID - but this is impossible and unbelievable at all.

>> - I do not know a possibility to generate keys that are composed by some
>> other values (f.e. an object-number consists of 4digits for the actual
>> department when it is created and 6 digits counting 0..999999). To do
>> this I had to use a stored procedure, that evaluates the key

> Relational theory says that
> a) sequences have no meaning, excecpt as a (semi-random) unique
> identifier
> b) a column describes a single value and only a single value

> Thus having any column where the 1st n digits mean ABC and the last x
> digits means ZYX.. that is oh so wrong that it makes me want to reach
> for my lead pipe and start foaming at the mouth.
Theorie tells also that if there is a natural key so do not invent a new one. And this key is needed for other applications, so that it must be build anyway.

>> - our special case:

> There are no special cases in relational theory. IMO. You either do it
> correctly. Or you don't.

Maybe there are no special cases in theorie. But if there are relevant reasons in practice so you should investigate, whether the theorie should be improved. There were some theories that had to be changed. (earth is a plate, nobody will need more then 640kB Storage, there is a worldwide market for about 5 computers ...)

>> 1 Database but Several Departments sometimes without connection
>> So we will solve this problem by doing the following:
>> * When connection exists the historical data are copied to the small dbs
>> (XML or something like that) and the moving date (?) are copied to the
>> database. Because of every department only inserts his own data there is
>> no trouble with data but with a simple sequence (remember, sometimes
>> there is no connection). If we use GUID we will (hopefully) fix this
>> problem.

> This architecture sounds very flawed and very problematic to me. Oracle
> connections can be stateless in the sense that they can service a
> stateless web client. I find it hard to see the logic in pumping data
> into XML and then pumping that into the database.
Even if you had a web- client: No connection/no work. This programs is used by persons, who are paid for give data into the database the whole day. If there are 2 hours or even 2 days without connection (or a very slow connection) the acceptance of the program will collapse. The actual way they work is, that everyone has his own database and 12 times a year the snapshots are given to build a unique database. Besides you have to put all the historical data (I do not know the exact english phrase) to all small databases. Because of that never happens at the same time it is a hard work to build this database (consistent). There must be another solution.

> It is a fact that dealing with batch processing you will have
> exceptions that will screw up your data that will require a lot of
> frustration and sweat and tears (never mind the drastic increase in
> coffee consumption) to solve.

> And what for? Not saying that this is it in your case Andreas.. heck, I
> have no idea what your arguments and reasons are for your XML decision
> - so do not take the following personally at all:
We decided to use XML, because the .NET- Komponents handle that nearly as easy as DataTables so the internal changes in Application are small. We surely could have decided to use a local database, something like Access or Interbase, but we think it will be easier (more comfortable and performant) not to install one mor database on client system.

> but too many times I have seem XML being used just because it is buzz
> word, it is "kewl" - and that is just plain and utter bullshit. (that
> said, XML is freakingly kewl when dealing with speech recognition rule
> sets - so I'm not anti-XML and pro-whatever)
I have no special meaning to XML, in our situation it seems to be suitable.

>> See the paragraphes above: If I could f.i. give sys_guid() or MyFunction
>> as a Default value the application development is much easier. You could
>> use this value directly for Master-Detail-Relations if you want, you
>> could use sysdate ore som functions depending on sysdate by default ...
>> AFAIK this feature is in more than 1 Database, why shouldn't it in
>> Oracle?

> True that it is a feature in other databases. Yes, I agree it should be
> one in Oracle too.

Fine

> I'm simply questioning the reasoning for wanting to do it that way.
> I've heard some counter arguments why that is a bad idea and to be
> honest, I find it hard to say why it would be a good idea despite me
> always doing sequences via triggers in Oracle.
Oracle comes along with thousands of possibilities which I am convinced nobody on earth knows all of them. I am convinced too that every developer/dba only uses a little of the features, not only because he doesn't need, but he doesn't know. But - so please do not take thos peronally - if there is a question an oracle- guru can not answer the answer will be like:

- nobody needs
- do not
- come to where the flavour is, come to oracle county

This doesn't depend on topic, it is only because the holy oracle is questioned.
(Examples from the last month:
- someone wanted to know, whether/how to store/execute SQL-Queries in DataTables
- someone wanted to know, whether there is a possibility, not to show columns that only contain NULL- values)
And there are some reasons to question the oracle way. (9 versions without a possibility to shrink an nearly empty datafile, tools, that are far away from modern, like SQLPlus, SQLPlus-WorkSheet ...)

> --
> Billy

But, thanks for your answers and thanks for that interesting conversation

Andreas

-- 
wenn email, dann AndreasMosmann <bei> web <punkt> de

Received on Mon Jun 20 2005 - 11:38:08 CDT