Re: Table design question

From: --CELKO-- <joe.celko_at_northface.edu>
Date: 23 Jan 2004 10:05:35 -0800
Message-ID: <a264e7ea.0401231005.19cd4ac4@posting.google.com>

>> The one-field [sic] user table smells like a design problem.
Merging user and user_history tables introduces ugly problems with relational integrity. <<

First of all, fields and columns are totally different. Next, there is no such thing as just "id"; I think that you meant to follow ISO-11179 and use "user_id" instead. Also, why did you use an ASCII picture instead of using DDL; I have no idea what the DRI actions should be.

CREATE TABLE Users
(user_id INTEGER NOT NULL PRIMARY KEY,
 ...);

CREATE TABLE Calendar
(cal_date DATETIME NOT NULL PRIMARY KEY,  ..);

CREATE TABLE User_History
(user_id INTEGER NOT NULL

        REFERENCES Users(user_id)
        ON UPDATE CASCADE,

 event VARCHAR(15) NOT NULL,
 start_date DATETIME NOT NULL,
 finish_date DATETIME, -- nulls means still current  CHECK (start_date <= finish_date),
 ...);

>> where I'm really not interested in historical data but just the
identity of the user, and those where exact user properties in point of time matter. <<

You can Google the detials, but the idea of a temproal database is to use durations and not points in time (see Zeno and Einstein for the nature of time).

It is also a good idea to have a calendar table to track holidays, workdays, fdiscal calendars, etc. in the enterprise. You use a BETWEEN predicate to find who was doing what on a given date. Received on Fri Jan 23 2004 - 12:05:35 CST