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

Don't really understand much of this, I regret to say. There seem to be jobs scheduled in weeks AND months AND seasons, which can't (or mustn't) happen. We also appear to have three tables instead of one.

I think using this scheme I would also have to pre-think every possible combination of weeks and days and seasons.

But the point is that there is *no* correlation between them at all. In Australia, Week 1 is in the middle of Summer. In Darkest Yorkshire, it's deepest Winter at the same time. It's up to the user to impart a meaning to "Week 1", and the creation of a table like CALENDAR would stop that flexibility.

Jobs also can't be scheduled irrespective of asset. We might Prune the rose beds on one housing estate in Week 14, and in another housing estate in week 16. Same asset, same job, different schedule.

But, I commend and appreciate the thinking, time and effort that went into this, and if I've missed the point, my apologies.

Regards
HJR "Galen Boyer" <galenboyer_at_hotpop.com> wrote in message news:u1yb117w7.fsf_at_hotpop.com...
> On Thu, 20 Jun 2002, dba_at_hjrdba.com wrote:
> > 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.
> >
>
> create table calendar
> (
> calendar_id number
> ,week_nbr number
> ,week_name varchar2(20)
> ,day_nbr number
> ,day_name varchar2(20)
> ,season_nbr number
> ,season_name varchar2(20)
> );
>
> This one is static and someone has to go through the pain of creating
> the rows for the near future, at least.
>
> create table job_schedule
> (
> jobschedid number
> ,jobcode number
> ,calendar_id
> );
>
> Maintain your jobs irrespective of the asset.
>
> create table asset_schedule
> (
> asstcode
> ,jobschedid number
> )
>
> Maintain your asset's schedule by tying to the job_schedule.
>
> > 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.
>
> SQL>select * from calendar;
>
> CALENDAR_ID WEEK_NBR WEEK_NAME DAY_NBR DAY_NAME SEASON_NBR
SEASON_NAME
> ----------- ---------- -------------------- ---------- -------------------
- ---------- -----------
> 1 1 WK1 1 Monday 1 Winter
> 2 1 WK1 2 Tuesday 1 Winter
> 3 1 WK1 3 Wednesday 1 Winter
> 4 1 WK1 4 Thursday 1 Winter
> 5 1 WK1 5 Friday 1 Winter
> 6 2 WK2 1 Monday 1 Winter
> 7 2 WK2 2 Tuesday 1 Winter
> 60 8 WK8 1 Monday 2 Spring
> 60 8 WK8 2 Tuesday 2 Spring
> 1000 1 WINTER
> 1001 2 SPRING
> 2000 1 WK1 2
> 2001 2 WK2 2
> 2051 52 WK52 2
>
> Use 1000,1001 for the vague seasons and >2000 for vague weeks.
>
> --
> Galen deForest Boyer
> Sweet dreams and flying machines in pieces on the ground.
Received on Thu Jun 20 2002 - 22:19:28 CDT