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

"Paul Dixon" <paul.gp.dixon_at_bt.com> wrote in message news:aes9s3$r38$1_at_pheidippides.axion.bt.co.uk...
> Howard,
>
> I don't know if this helps any but you could replace the four date related
> field with just two date fields e.g. earliest_start and finish_by. The
> values in these fields can then be related to a separate table which has
> entries that define all of the periods.
>
> If nothing else it should simplify the primary key a little.
>

The 'assets' are things like trees and shrub beds. You don't schedule a prune or a lopping for a specific day, unless your address is 'Heaven' and you can control the weather. So specific dates are meaningless in this table. The best you can do in real life is a week. And the 'day' is there merely to allow the same job to happen multiple times in a week, such as leaf clearing.

So real dates and hence date ranges are out of the question, I'm afraid. But I appreciate your thoughts nonetheless.

Regards
HJR
>
> --
> Paul Dixon
> paul.gp.dixon_at_tinnedham.bt.com
> (Remove canned meat to reply)
>
> "Howard J. Rogers" <dba_at_hjrdba.com> wrote in message
> news:aerjeu$guk$1_at_lust.ihug.co.nz...
> > 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.
> >
> > The scheduling options are mostly mutually exclusive: if you say
'sometime
> > in Spring', you can't then say you want it done in Week 16. Either you
are
> > vague, or you are specific.
> >
> > The exception is the week/day combination. You might want a job
performed
> > each Tuesday and Thursday of week 16, so using both the week and the day
> > columns is permitted.
> >
> > My trouble is that since an assett can have many jobs scheduled for it,
> and
> > each job can be scheduled many times, the entire table is the entire
> primary
> > key.... and that doesn't feel right to me. I've actually created this
> table
> > as 'ORGANIZATION INDEX', so if it *is* right, I can cope as best as
> > possible.
> >
> > But are there any other suggestions? (And feel free to criticise the
> > design/understanding of the relational model and so forth. I first
created
> > this table about 12 years ago. I've not seen an easier or more
appropriate
> > way of doing it before now, but one can always learn).
> >
> > Regards
> > HJR
> >
> >
>
>
Received on Thu Jun 20 2002 - 15:25:29 CDT