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

On Fri, 21 Jun 2002, dba_at_hjrdba.com wrote:

> 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.

I thought about this. I'm not sure how you ensured this with your design so I didn't worry about it. Those are business rules really, not data integrity, so I would guess you do it at the app level.

> We also appear to have three tables instead of one.

Thats the idea. Put things that are of the same ilk in one table and maintain that entity irrespective of the other relationships. Then, tie them together with ids/fks.

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

No. It isn't a cartesian product. The calendar table, at it lowest denominator, has a row for each day. Each day will have a week number, and a season number. But, to satisfy the vagueness requirement, I also added rows that only have seasons and days that only have weeks.

> 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.

Can one user say that its summer time in Australia, while another says that its wintertime in Australia for the same date? Or can one user say its "summertime" in Australia while another user says its "Damn Hot" in Australia for the same date.

> 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.

That was the idea behind the jobs, job_schedule and asset_schedule tables. (Although, it sounded like a jobs table already existed)

The jobs table would be were you maintain jobs.   job_id job

• ------------- 10 prune rose beds

The job_schedule table was were you maintained when jobs were scheduled
  job_sched_id job_id calendar

• ------- ---------- 1 10 "in week 14" 2 10 "in week 16"

The asset_schedule was were you said that a particular asset was scheduled for a particular job.

  asset_id   asset_name           job_sched_id
  -------    -----------          ------------
  1          Housing estate1      1
  1          Housing estate1      2
  2          Housing estate2      2

-- 
Galen deForest Boyer
Sweet dreams and flying machines in pieces on the ground.