Link to home
Create AccountLog in
Avatar of 25112
25112

asked on

levels of normalization.

is the below too normalized? or just fine? anu suggestions to tighten or loosen?
Avatar of 25112
25112

ASKER

erd
user.pdf
Avatar of 25112

ASKER

the erd in plain text:

User <-> UserType (many to one) - Many Users can have one UserType
User <-> Contact (many to one) - Many Users can have one Contact
      Contact <-> ContactSuffix (many to one) Many Contacts can have one Suffix
      Contact <-> ContactPrefix (many to one) Many Contacts can have one Prefix
User <-> UserAgency (one to many) - One User can have multiple Agencies
User <-> UserBureau (one to many)- One User can have multiple Bureaus
User <-> UserRole (one to many)- One User can have multiple Roles
User <-> CustomFolder (one to many)- One User can have multiple CustomFolder
User <-> UserProfile (one to many)- One User can have multiple Profiles
User <-> UserGroupMember (one to many)- One User can have multiple GroupMember
      UserGroupMember <-> UserGroup (many to one)- Many UserGroupMember can belong to one UserGroup
User <-> UserAddress (one to many)- One User can have multiple Addresses
UserAddress <-> Address (many to one) Many UserAddress can have one Address
      Address <-> State (many to one) Many Address can have one State
SOLUTION
Avatar of kenamb
kenamb
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of 25112

ASKER

OK- thanks. what level would you consider is the attached erd?
SOLUTION
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of 25112

ASKER

scott, can you give a small example of transitive dependencies..

the tbl thing - yeah.. i will try to convince...

here is the text.. this is just related to what you saw above and some dependencies.. all related to logindetails before authentication.
script.sql
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of 25112

ASKER

>>A quick example of transitive dependency, since I often see this one: Project ( Project_ID, Title, Manager, Manager_Phone )
thanks.. helpful.

>>Is Bureau an attribute of User or Group?
thanks- i think you caught something.. user can belong to bureau's.. with the above, it can be redundant... will check into that.

>> One other thing looks unnormalized though:      [EmailAddress] [dbo].[EmailType] NOT NULL, --can be more than 1
can you explain what do you mean by "can be more than 1".. do you mean Contact have more than one emailaddress? Are you suggesting to keep WorkEmailAddress,Homeemailaddress etc?

have you written any article on when to use identity and not, and and when to use identity to be the pk?
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of 25112

ASKER

great points, Scott.. thanks.

>>All those Active flags will kill you when it comes to performance, however.
OK.. but does this also depend on how many rows in the table..? like if less rows, then flags are Ok and vice versa?
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of 25112

ASKER

>>The active flag forces a table scan for most queries.
so in general, you would suggest totally skipping BIT fields as much as possible. (if related to INACTIVE status).. what about any other business logic unlike CREDITED (flag Yes/No), COMMUNICATED (yes/No).. are they also in the same category?


>>But if a proper index would otherwise do it in 50 I/Os rather than a table scan
are you referring to an index on the bit field? or index on another set of columns? (to replace the BIT usage)
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of 25112

ASKER

thanks Scott-makes sense about bit fields..

have you ever had to use/justify use of bit fields in your design or business need, because of no other choices? or how did you overcome that situation?
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
ASKER CERTIFIED SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of 25112

ASKER

thanks Scott