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

From: Galen Boyer <galenboyer_at_hotpop.com>
Date: 20 Jun 2002 22:02:28 -0500
Message-ID: <u1yb117w7.fsf@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:02:28 CDT