Normalization (DB Design)
-
Is my table schema normalized? Please Suggest.
-
It would be a lot easier if you could post the actual ddl (create table scripts and indexes, keys etc).
It does look like you need to add some primary keys to most of your tables. In general it looks like you are close.
Post the ddl and you get lots of help.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ -
I think the best answer we can give you is "we haven't a clue". What you've given us is a pretty picture with no indication of what the primary keys are or even whether there are any (so we can't tell if it's in 1st normal form), which attributes are constrained not to be null and what uniqueness constraints there are, what the foreign key constraints are. We don't know what business rules imply in terms of functional dependencies between the various attributes so even if we knew all about the keys, not null constraints, and foreign key constraints we couldn't tell whether it is in 2NF or 3NF or EKNF or BCNF (or even whether BCNF is a sensible option for all of the schema). We don't know what multivalued dependencies are implied by the business rules but not implied by functional dependencies, so we can't tell whether it's in 4NF or whether there any parts where going to 4NF would violate the representation principle. We don't know what join dependencies there are so we can't determine whether it's in 5NF. We don/t know what check constraints there are so that we haven't a clue whether any business rules implying domain constraints can be enforced by the schema - that's again part of 1NF, according to some definitions; it's certainly part of the original 6NF, now called DKNF, and probably part of Date's new 6NF (which is roughly speaking about additional rules for data where many attributes have many values each of which has a set of time ranges of validity), if it isn't included in any lower normal form.
What you perhaps don't understand is that a schema is normalised to a certain level when certain classes of business rule are directly enforced by the schema's structure, keys, and constraints; that means that in general it's impossible to know whether the schema is normalised to that level without knowing what business rules exist up to that level and what keys and constraints the table has. A pretty picture with no indication of keys and constraints is a starting point, but not enough to get anywhere answering the question "is this normalised".
It could also help if you indicated what level of normalisation you require - generally people think anything lower than 3NF is a mistake (and I reckon that anyone who understands the stuff thinks anything lower than EKNF is usually a mistake). But most people think aiming for 6NF (either sort - we don't even know whether DKNF is achievable for most schemas, and Date's 6NF is a very rare requirement) is going too far except in very special cases, and that even aiming for 5NF may be too much. But most databases in EKNF will be in BCNF, 4NF, 5NF and often also Date's 6NF without any further normalisation anyway, so that people don't aim for 5NF doesn't necessarily mean that they don't get there - in fact that's often considered the ideal: you go to EKNF and discover that by pure luck you happen to be in 5NF too.
Tom
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply