Re: normalization problems

From: Vaughan Powell <vaughan_at_workmail.com>
Date: Wed, 03 Jan 2001 11:04:25 GMT
Message-ID: <92v0vo$pim$1@nnrp1.deja.com>

In article <3a44fac6.7134192_at_news.ntlworld.com>, kosmond_at_softhome.net wrote:
>
> As for the integer keyfields, my client wants the keyfields to be in
> the form AAA001, so I've got to do that, sadly. It's a pretty small
> database anyway in terms of number of records, so hopefully the
> performance won't be very appreciably affected.
>
> Thanks again!
>
> -Karen.
>
>

Karen,

Your client has specified that they want 'keyfields' in the form AAA001, but this should not be confused with the design of the database physical structure. For example, if AAA001 is a product code, your table structure could be be something like:

Product:

ProductId          integer      PRIMARY KEY,  -- Auto generated number
ProductCode        char(6)      UNIQUE KEY,   -- Holds AAA001 structure

ProductDescription varchar(100)

Foreign key relationships will reference product via ProductId not ProductCode. This has the following benefits:

More efficient joins
Less storage used on tables with foreign key relationships to Product
Higher degree of normalisation - changes to ProductCode can be implemented with a single row update to the Product table - watch out for this as the client will inevitably say that their codes do not change. What they usually mean is that their codes do not change very often!!!

The user does not ever have to see the ProductId on the screens, instead the ProductCode is displayed.

I come from a traditional mainframe database background where we always used to design database tables the natural keys, usually character codes, as primary keys, but have since been through a process of reeducation and am now fully convinced of the benefits of using integer keys that do not contain data that is meaningful to the users (surrogate keys).

Good luck with the implementation.

Regards

--
Vaughan Powell MCDBA, MCSD, MCSE
Data Architecture and Applications Design Manager
BuildOnline


Sent via Deja.com
http://www.deja.com/

Received on Wed Jan 03 2001 - 05:04:25 CST