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

Hi Niall,

Thought of that, and didn't like it because these are (or would be if this was for real) physical things to which jobs are being done, and I want it to be impossible for the same job to get scheduled twice in the same day to the same thing. A sequence would just spit out a unique number, and keep the primary key happy for sure, but then it would be a free-for-all on scheduling.

In a previous existence, I came up with something similar, though. Each season got a number -SPR=1, SUM=2, AUT=3, WIN=4. Each month got a number 1 to 12 (or 1 to 13 if you were dealing with 13 4-week periods). Each week got a number+50 (hence week 16 would get coded as number 66). Each day got given a decimal point (MON=0.1, TUE=0.2 and so on). Add the lot together, and you get an identifying "schedule number". And I built the primary key on (assetcode, jobcode, schedule number). Recalling that you can't schedule a job in a season *and* a month *and* a week (it's one or the other), that scheme was unique enough.

But it was a lot of coding, and I was hoping to avoid it this time.

From your thouhts and the thoughts of others, I reckon I'd be better off just using IOTs.

Thanks for the time and trouble, though.

Best regards
HJR "Niall Litchfield" <n-litchfield_at_audit-commission.gov.uk> wrote in message news:3d11aa73$0$232$ed9e5944_at_reading.news.pipex.net...
> You could consider a meaningless key generated by a sequence.
>
>
> --
> Niall Litchfield
> Oracle DBA
> Audit Commission UK
> *****************************************
> Please include version and platform
> and SQL where applicable
> It makes life easier and increases the
> likelihood of a good answer
>
> ******************************************
> "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:41:42 CDT