What do you do with an ENORMOUS primary key?

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 Wed Jun 19 2002 - 22:50:25 CDT