Chris,

I must confess that the scope of changes your system will require is perhaps
beyond scope of this list but it could be a useful exercise for all to
participate in.

Here is how I would approach it.

First of all, is the CO, PARCEL pair mandatory throughout the entire system?
This could be where the surrogate key could come in.

Call it CP_ID and add it to all of your tables. Then, make a generator for
it and set it to a number around 999999 so that you will keep a nice fixed
line of numbers for your surrogate key. Then in the 15% of your master
tables perform an update something like this:

UPDATE MASTERTABLE
SET CP_ID = GEN_ID( GEN_CP_ID, 1 )

Once you have done one of the master tables then I suppose it would be
necessary to propagate those key values to the other tables that have the
same CO, PARCEL values. Once they have been propagated you can then remove
those columns from the target tables. Or, if they are needed for direct
searching, etc. you can have them become trigger maintained. I prefer to
avoid that if possible though.

The rule of thumb here is (for me anyway) don't use any information that is
externally meaningful to form your primary and foreign key relationships.

HTH,
Jason Wharton
CPS - Mesa AZ
http://www.ibobjects.com

----- Original Message -----
From: "Chris Landowski" <chrisl@...>
To: <IBObjects@egroups.com>
Sent: Thursday, January 18, 2001 9:38 PM
Subject: Re: [IBO] KeyLinks & Primary Keys


> Jason,
>
> > What are the dimensions of the unique key columns? If they are sizable
> then
> > I strongly discourage you using them in the KeyLinks property. If they
are
> > large they get duplicated and use up more memory than otherwise. Keys
and
> > data are buffered separately with IBO.
> >
> > Is there a reason you cannot just use an integer surrogate key column?
>
> I had a brief discussion on this topic with Helen and am really interested
> in implementing a surrogate key but cannot figure out how to make it work
> with the current structure of the database. From a unique key standpoint,
my
> database is basically laid out as follows:
>
> 15% of the master tables have a unique key of CO, PARCEL
> 35% of the master tables have a unique key of CO, YR, PARCEL
> 25% of the master tables have a unique key of CO, YR, PARCEL, DWELLING
> 25% of the master tables have a unique key of CO, YR, PARCEL, STRUCTURE
> I also have about 100 code tables used to drive calculations with a unique
> key of either CO, CODE or CO, YR, CODE.
>
> At login, the user selects a community and year (CO, YR) to work with
> parcel, dwelling and building structure related information.
>
> Using a surrogate key would certainly cut down on the size of the database
> and most like cause the optimizer to behave much better, so that I would
not
> have to constantly hard code in a plan.
>
> Could you or someone on this list, PLEASE explain how I would go about
> creating surrogate keys in this situation ? Any brief examples would be a
> great help.
>
> Thanks,
>
> Chris Landowski
> Dynamic Software Solutions
>
>
>
>