|
|
Posted by --CELKO-- on 10/08/05 14:52
You have tables without keys, use BIT and IDENTITY. Can you explain
what the heck a date_id would mean? Why do you think that a date is
not unique in itself? You also keep saying "fields" and
"records" so we know that you are designing a file system and not
an RDBMS.
>> A student's most recent status record [sic] always has an end date of '12/31/9999 12:00:00 AM' <<
This is a really bad idea for a lot of reasons. Ignoring the use of
"record", a status is an attribute and not an entity. Cleaning up just
a little, we have:
CREATE TABLE Calendar -- 10-20 years is usually enough
(cal_date DATETIME NOT NULL PRIMARY KEY,
date_type CHAR(1) NOT NULL);
CREATE TABLE Students
(student_id INTEGER NOT NULL PRIMARY KEY,
student_status CHAR(2) NOT NULL);
CREATE TABLE DailyAttendance
(student_id INTEGER NOT NULL
REFERENCES Students(student_id),
attendance_date DATETIME NOT NULL,
site_id VARCHAR(6) NOT NULL,
attend_code CHAR(2) NOT NULL);
History tables are keyed with the entity and the start date of an
event, not with IDENTITY unless you meant to destroy data integrity.
CREATE TABLE StudentActivityHistory
(student_id INTEGER NOT NULL,
status_startdate DATETIME NOT NULL,
status_enddate DATETIME,
activity_status CHAR(2) NOT NULL,
PRIMARY KEY (student_id, status_startdate))
CREATE TABLE StudentSiteHistory
(student_id INTEGER NOT NULL,
site_id VARCHAR(6) NOT NULL,
site_startdate DATETIME NOT NULL,
site_enddate DATETIME,
PRIMARY KEY (student_id, site_startdate));
Why is there not a general table like this instead?
CREATE TABLE StudentHistory
(student_id INTEGER NOT NULL,
startdate DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
enddate DATETIME, -- null means current
CHECK (startdate < enddate),
activity_code CHAR(2) NOT NULL
CHECK (activity_code IN (..)),
site_id VARCHAR(6) NOT NULL
REFERENCES Sites(site_id)
ON UPDATE CASCADE,
PRIMARY KEY (student_id, startdate));
Now your question is easy -- you have an event anchored in both time
and space, like it should be. And the DailyAttendance table is
redundant; the current day has (enddate IS NULL).
Navigation:
[Reply to this message]
|