Re: NextNumbers Tables

Thanks for that Alan.

Unfortunately I only have DB2 5.1 (AS/400).

To be honest I could not give a monkeys if all my tables are fully normalised :-). If they do their job and it makes developing easier (= faster = less cost) the customer is happy and that is my job. So from this respect would it be better to use surrogate keys as primary keys?

No offence intended. It has just been drummed into me that it is better to get something working sooner rather than working on all the theory, and I have seen this in practice: ppl who sit + wonder about all the theory tend to take far longer to deliver projects if at all. I like looking at this NG to see if I can take anything to help me be a better designer but I like to KISS (Keep It Simple Stupid)!

Cheers
Stu

cheers
Stu

"Alan Gutierrez" <ajglist_at_izzy.net> wrote in message news:0dS1a.21891$tQ1.1438185_at_news2.east.cox.net...
> stu wrote:
>
> > "Alan Gutierrez" <ajglist_at_izzy.net> wrote in message
> > news:1XO1a.20790$tQ1.1398507_at_news2.east.cox.net...
>
> >> I don't know if you are listening but, don't use a next number. Find a
> >> a natural primary key.
> >
> > Why? My uni teacher used to say always use a surrogate key. It stops
> > you from duplicating keys later. What advantages does it have over
> > using IDs?
>
> The word "always" is a red flag in computer science.
>
> How are surrogate keys going to prevent you from duplicating keys later?
> I'm not sure I understand what you mean by 'duplicate a key'.
>
> Bernard Peek offered me the following:
>
> <quote>
> When a table has a real and surrogate key it is not fully normalised.
> That is the surrogate key is (you hope) completely dependent on the real
> key. But that isn't always certain. There are ways that dependency can
> be broken. When that happens your database is broken.
>
> Insert Anomaly: A new record can be inserted that duplicates all of the
> data from an existing record. But it will have a new surrogate key. The
> system will duplicate data.
>
> Update Anomaly: The data for the real key is changed, but without
> changing the surrogate. As a result the data in associated tables linked
> by the surrogate key now refers to the wrong person.
>
> Neither of these situations can be detected from within the database,
> they need additional processing systems to detect the anomalies, or the
> risk of anomalies. There may have to be manual processes or additional
> programs. This additional processing makes the system as a whole less
> robust. That is my main objection to using surrogate keys unnecessarily.
> </quote>
>
> >> If you absolutely cannot find one, use the sequence facilities
> >> provided by your db. Now you are going to have to find some other way
> >> to check for duplicate entries in your database. If there is no
> >> sequence facility provided by your db, get a different db.
>
> > I wish! DB2 as/400 dont have ID.
>
> From what I can Google, DB2 has a sequence facility as of version 7.2.
> Look for CREATE SEQUENCE.
>
> All the best on your project.
>
> --
> Alan Gutierrez - ajglist_at_izzy.net
Received on Tue Feb 11 2003 - 10:41:10 CET