|
|
Posted by --CELKO-- on 10/02/55 11:49
Have you considered a relational design? It would have actual keys of
a proper data type and some research about standards? Do you really
know anyone with a first_name that is VARCHAR(100)? Why did you think
that IDENTITY can ever, ever be a key? And a key with a DEFAULT?
Defaults are for attributes which can have multiple occurrences of
values. And why did think that you need a BIGINT so you can have more
users than the entire population of Earth?
Can you use the "Dictionary of Occupational Titles" for
Competencies? What research did you do?
Why is a user an attribute of a Competency? Only Fred can be a
carpenter? Why do you use bit flags in SQL?
CREATE TABLE Competencies
(dot_code CHAR(6) NOT NULL PRIMARY KEY,
dot_description VARCHAR(25) NOT NULL);
Shouldn't there be a user attribute for approval powers? You did not
show one.
CREATE TABLE Users
(user_id INTEGER NOT NULL PRIMARY KEY, -- needs research!
first_name VARCHAR(25) NOT NULL,
last_name VARCHAR(25) NOT NULL);
>> When I delete a competency record [sic] I need all references to this record [sic] to be deleted. When I delete a user I want to set the foreign key to zero (the record [sic] must remain on the database). <<
One of the first steps to learning to think in SQL is that a row and a
record are totally different. Until then, you will keep setting flags
(like your "foreign keys are set to zero" violation of 1NF).
You are trying to keep a history, so you need durations in the data
model.
CREATE TABLE Assignments
(user_id INTEGER NOT NULL
REFERENCES Users(user_id)
ON DELETE CASCADE
ON UPDATE CASCADE,
dot_code CHAR(6) NOT NULL
REFERENCES Competencies(dot_code)
ON UPDATE CASCADE,
assignment_date DATETIME
DEFAULT CURRENT_TIMESTAMP NOT NULL,
completion_date DATETIME
DEFAULT CURRENT_TIMESTAMP
CHECK (assignment_date < completion_date),
PRIMARY KEY (user_id, dot_code, assignment_date),
approving_user_id INTEGER NOT NULL
REFERENCES Users(user_id)
ON DELETE CASCADE
ON UPDATE CASCADE);
This is proper SQL, but SQL Server will not allow it. The product
worries about cycles in DRI.
One kludge might be to split out the approvers
CREATE TABLE Competencies
(dot_code CHAR(6) NOT NULL PRIMARY KEY,
dot_description VARCHAR(25) NOT NULL);
CREATE TABLE Users
(user_id INTEGER NOT NULL,
user_type CHAR(1) DEFAULT 'R' NOT NULL
CHECK (user_type IN ('R', 'A')), -- r= regular, a = approver
PRIMARY KEY (user_id, user_type),
first_name VARCHAR(25) NOT NULL,
last_name VARCHAR(25) NOT NULL);
CREATE TABLE Approvers
(user_id INTEGER NOT NULL,
user_type CHAR(1) NOT NULL
CHECK (user_type = 'A'),
PRIMARY KEY (user_id, user_type),
FOREIGN KEY (user_id, user_type)
REFERENCES Users (user_id, user_type)
ON DELETE CASCADE
ON UPDATE CASCADE);
CREATE TABLE RegularUsers
(user_id INTEGER NOT NULL,
user_type CHAR(1) NOT NULL
CHECK (user_type = 'R'),
PRIMARY KEY (user_id, user_type),
FOREIGN KEY (user_id, user_type)
REFERENCES Users (user_id, user_type)
ON DELETE CASCADE
ON UPDATE CASCADE);
CREATE TABLE Assignments
(user_id INTEGER NOT NULL,
user_type CHAR(1) NOT NULL CHECK (user_type = 'R')
FOREIGN KEY (iser_id, user_type)
REFERENCES RegularUsers(user_id, user_type)
ON DELETE CASCADE
ON UPDATE CASCADE,
dot_code CHAR(6) NOT NULL
REFERENCES Competencies(dot_code)
ON UPDATE CASCADE,
assignment_date DATETIME
DEFAULT CURRENT_TIMESTAMP NOT NULL,
completion_date DATETIME
DEFAULT CURRENT_TIMESTAMP
CHECK (assignment_date < completion_date),
PRIMARY KEY (user_id, dot_code, assignment_date),
approving_user_id INTEGER NOT NULL,
user_type CHAR(1) NOT NULL CHECK (user_type = 'A')
FOREIGN KEY (user_id, user_type)
REFERENCES ApprovingUsers(user_id, user_type)
ON DELETE CASCADE
ON UPDATE CASCADE);
Navigation:
[Reply to this message]
|