Re: Table design question
Date: 23 Jan 2004 10:05:35 -0800
Message-ID: <a264e7ea.0401231005.19cd4ac4_at_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. <<
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 - 19:05:35 CET