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

Howard,

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

I have a similar problem here. The company salesforce use calendar weeks months and years, the accountants use a financial year based around the tax year with month having exactly 28 or 35 days (4 or five weeks) which alsways starts on a Thursday, and the production systems use months which always end on the last Friday of the calendar month.

Our solution is a calendar table similar to that below which gives complete flexibilty :-

Calendar_type char -- e.g. F for financial S for Sales P for Production
Period_type char -- e.g. W for week M for Month Q for quarter etc. Period_name char -- e.g. 200101 for period 1 in year 2001 start_date date -- the date that the period starts end_date date -- the date that the period ends

Amongst other things we use the calendar for reporting purposes. It makes it easier to tell the accountants how many Widgets the sales people sold in an financial period, and the Sales people how many Widget the production people made in a calendar period, but it could just as easily be used for scheduling.

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

This is where I think some of the mis-understandings lie. I assumed that the Roses in housing estate A were a different asset to the Roses in estate B, albeit the same type of asset. I also assumed that pruning the Roses in housing estate A was a different job to pruning the Roses in housing estate B albeit the same activity.

--
Paul Dixon
paul.gp.dixon_at_tinnedham.bt.com
(Remove canned meat to reply)