Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> 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).
>
> Regards
> HJR
If you want a smaller PK, then you can create a dummy PK column and
create a unique index on have all the other columns.
For e.g
Create table STANDARDS (
id number (9) primary key,
asstcode varchar2(3),
jobcode number(5,0),
season varchar2(3),
period number (2,0),
week number (2,0),
day varchar2(3))
create unique index stand_uniq on STANDARDS ( assetcode, jobcode, ...)
OR
you could move the schedules to another table
create table SCHEDULES (
(scheduleid number (9,0) primary key,
season varchar2(3),
period number (2,0),
week number (2,0),
day varchar2(3))
and define the standards table as
Create table STANDARDS (
id number (9) primary key,
asstcode varchar2(3),
jobcode number(5,0),
scheduleid number (9,0))
-RK Received on Thu Jun 20 2002 - 07:31:31 CDT