Re: What do you do with an ENORMOUS primary key?

"Nuno Souto" <nsouto_at_optushome.com.au.nospam> wrote in message news:3d11b917$0$28007$afc38c87_at_news.optusnet.com.au...
> In article <aerjeu$guk$1_at_lust.ihug.co.nz>, you said (and I quote):
> > Suppose I have a table as follows:
> >
> > Create table STANDARDS (
> > asstcode varchar2(3),
> > jobcode number(5,0),
> > season varchar2(3),
> > period number (2,0),
> > week number (2,0),
> > day varchar2(3))
> >
> > In other words, an asset can have all sorts of jobs performed to it, and
> > those jobs can be scheduled to occur 'sometime in Spring', or 'sometime
in
> > March', or sometime in week 16, or on Thursday.
> >
>
> Hmmmm, just a few thoughts:
>
> ASSET table. With obviously asset code there, plus whatever else you may
> need.
>
> JOB table. With jobcode there, plus whatever else you may want to define
> the job with (interruptible, recoverable, restartable, that sort of
> jazz).
>
> JOBSCHED table. Intersects both of the above. With the
> date/timespan attributes there.
>

This is in fact what I've got. Your JOBSCHED is my STANDARDS -what do you do to an asset and when.

> In practice, you may well find that having surrogate keys (PKs out of
> sequences and generated by trigger) is a better solution than using the
> *code attributes as your PKs.
>
> This mostly because in a hierarchy, if anyone wants to reassign the code
> of a given product and that code is sprinkled all over the place as a FK,
> you have a major task in your hands.
>
> With a surrogate key, the code is just a unique key attribute. The
> surrogate key is all over the place as a FK, but you never need to update
> it. Just create or delete.
>
> Just my 0$02 worth.

Mmmm. Thanks. Interesting that both you and Neill see a synthetic primary key looming over the horizon. I don't think that will work, because then there's no guarantee that you won't schedule a rose bush to be pruned twice in one day -and a poor looking specimen it would be then!! I've never liked synthetic keys (like Microsoft's autonumber) for just that reason: it's every appearance of primary-keyness, but doesn't actually guarantee uniqueness for your real data. But that's a philosophical discussion that can run and run, I think!

But thanks anyway for thinking about it.

Regards
HJR
>
> --
> Cheers
> Nuno Souto
> nsouto_at_optushome.com.au.nospam
Received on Thu Jun 20 2002 - 15:30:43 CDT