|
|
Posted by Jeff North on 10/02/48 11:49
On 5 Jun 2006 10:52:35 -0700, in comp.databases.ms-sqlserver
"--CELKO--" <jcelko212@earthlink.net>
<1149529955.519214.221850@i39g2000cwa.googlegroups.com> wrote:
>| 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?
Please don't jump to unnecassary, and wrong, conclussions.
I'm converting the database from mySQL4.0.21 to MSSQL therefore the
schema hasn't been fully optimised.
>| Can you use the "Dictionary of Occupational Titles" for
>| Competencies? What research did you do?
Again, you've seen 3 partial tables and jump to unnecessary
conclusions.
>| Why is a user an attribute of a Competency? Only Fred can be a
>| carpenter? Why do you use bit flags in SQL?
They, bit flags, are the most appropriate type for true/false or
yes/no or in/out data fields, yes?
>| 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);
Again, you're jumping to wrong conclusions.
The usr_info table is for registered users that can add, delete and
modify data. I need to track who has done what as part of the business
rules for the application (as can be seen by the ApprovedBy and
LockedBy fields).
>| >> 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.
Please explain this further.
>| Until then, you will keep setting flags
>| (like your "foreign keys are set to zero" violation of 1NF).
>|
I've snipped your code but will study it further.
---------------------------------------------------------------
jnorthau@yourpantsyahoo.com.au : Remove your pants to reply
---------------------------------------------------------------
Navigation:
[Reply to this message]
|