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

"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).

You're packing a lot of information into your STANDARDS table. I would tend to use a small group of schedule tables instead: yearly, seasonal, monthly, etc. A field in the job record could tell me which schedule table(s) the job could appear in.

Even if you prefer to use your STANDARDS table as you describe, analyzing the problem as if you had multiple schedule tables should help you design your STANDARDS table to do what you want it to do. Alternative point of view, that sort of thing.

Just a thought.

Joel Rees Received on Sun Jun 23 2002 - 20:24:46 CDT