SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Design


Design

Author
Message
Stewart "Arturius" Campbell
Stewart "Arturius" Campbell
SSCrazy Eights
SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)

Group: General Forum Members
Points: 9243 Visits: 7283
Comments posted to this topic are about the item Design

____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
UMG Developer
UMG Developer
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2756 Visits: 2204
I have to disagree with what you have as the correct answers for this question. The question states "According to Microsoft SQL Server Books online, what principles are not adhered to in this design?" and in the link you provided it does not list "Multiple identifier columns" as a problem.

In addition the OrderNo column is NOT an identifier because it does not have a constraint defined on it to prevent duplicate values, or at least the DDL shown doesn't show a constraint. (Yes I know you would have to turn identity insert on for the table, but that can be done, so that field just defaults to be a sequentially numbered field, but it could contain duplicate values.)
Hugo Kornelis
Hugo Kornelis
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11428 Visits: 12007
I agree with UMG developer about the multiple identifier. I already had this option checked before deciding to check BOL first. I disagree with him about the missing identifier - the OrderRefNo does have both the NEWID() default and the PRIMARY KEY constraint to make this a good identifier.

But what bothers me more than losing a point, is that we are asked to answer according to BOL. When it comes to good database design, BOL is about the last reference I'd use! I must admit that the page about normalization that is referenced here is not as bad as I feared, but there are still errors.

The first bullet point in BOL:
"The fundamental rule of database design theory is that each table should have a unique row identifier, a column or set of columns used to distinguish any single record from every other record in the table. Each table should have an ID column (...)"
This starts okay, but then detoriates. The first sentence says that the identifier can be a single column or a set of columns. The very next sentence mentions only the ID column, as does the rest of this bullet point. It does not explicitly say "identity or newid()", but it's awful close.
As a result, many tables in SQL Server, including the one in the question, lack a "real" key. Neither IDENTITY, nor NEWID() are real keys. These are generators for surrogate keys, but a surroogate should always be backed by a real key. In the case of the table in the question, what will protect the system from duplicate entries because a clerk accidentally hits the "Add row" button twice? (Or even falls asleep with his finger on the button)

Third bullet:
"A table should avoid nullable columns."
No. A table should avoid unnecessary nullable columns. When values may be missing in a column, making the column nullable is actually the best way to handle this. This advice leads to columns that are made not nullable and than use some magic value to represent a missing value. The argument about complexity of handling nulls is nonsense; handling the magic values requires even more complex special handling. The default behaviour of nulls usually matches how people want missing information to be treated.
The idea to put nullable columns in a seperate table only makes sense if all these columns are linked to a single subtype, so that they all are not nullable in the new table. And even then, the use is limited - the downside of splitting these columns to their seperate table is the introduction of outer joins in many queries. And the "special handling" is not avoided, since these outer joins throw the nulls right back in your face.


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
UMG Developer
UMG Developer
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2756 Visits: 2204
Hugo Kornelis (9/23/2010)
I agree with UMG developer about the multiple identifier. I already had this option checked before deciding to check BOL first. I disagree with him about the missing identifier - the OrderRefNo does have both the NEWID() default and the PRIMARY KEY constraint to make this a good identifier.


Hugo,

I agree that OrderRefNo is an identifier, but if you look at my comment I was saying that OrderNo is not an identifier:

OrderNo INT NOT NULL IDENTITY(1,1),



So there aren't multiple identifiers in the table. Given that "Multiple identifier columns" isn't an option for a principle that the DDL violates.
tommyh
tommyh
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1804 Visits: 2000
Have to agree with UMG Developer. The "Multiple identifier columns" shouldnt be part of the answer for 2 reasons.

1. Its not part of MS recommendations (atleast not on the page thats linked in the answer)

2. In the answer you have yourself added
While having multiple identifier columns is not, strictly speaking, against the rules, it makes the table design overly complex, wastes space and adds no value

So your contradicting yourself. Now i do agree that that might not be the best solution but that doesnt make it wrong.

Although i did select the "Nullable columns" option as well i dont really agree with that. It sounds nice in "theory". But from a practical point of view its not. Because if your building a system that will have ANY form of human interaction it just wont work. Unless you want to put every datacolumn into its own table and have tables like CustomerSurName/CustomerLastName/CustomerMiddleName. Instead of just CustomerName. This is one "rule" that should be used real sparingly.

/T
Hugo Kornelis
Hugo Kornelis
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11428 Visits: 12007
UMG Developer (9/23/2010)
I agree that OrderRefNo is an identifier, but if you look at my comment I was saying that OrderNo is not an identifier:

OrderNo INT NOT NULL IDENTITY(1,1),



So there aren't multiple identifiers in the table. Given that "Multiple identifier columns" isn't an option for a principle that the DDL violates.

Sorry, UMG. You are right. I saw your comment and interpreted it as defending the "no identifier at all" option - my bad, I should have read better.


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
mp3killa 9680
mp3killa 9680
SSC Rookie
SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)

Group: General Forum Members
Points: 41 Visits: 54
i'm not a DBA by trade, but the multi identifier column issue seems more like an attempt to "catch you out" than to make one feel good about one's acquired knowledge of SQL... just puts a nasty damper on the poor C# developer who does as best he can...

all fair in love and war i suppose...
si_downes
si_downes
SSC-Addicted
SSC-Addicted (444 reputation)SSC-Addicted (444 reputation)SSC-Addicted (444 reputation)SSC-Addicted (444 reputation)SSC-Addicted (444 reputation)SSC-Addicted (444 reputation)SSC-Addicted (444 reputation)SSC-Addicted (444 reputation)

Group: General Forum Members
Points: 444 Visits: 217
and no mention of the clustered index on the GUID. Might not be on the referenced page of BOL but not a great idea
Rune Bivrin
Rune Bivrin
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3920 Visits: 1502
Hugo Kornelis (9/23/2010)
Third bullet:
"A table should avoid nullable columns."
No. A table should avoid unnecessary nullable columns. When values may be missing in a column, making the column nullable is actually the best way to handle this. This advice leads to columns that are made not nullable and than use some magic value to represent a missing value. The argument about complexity of handling nulls is nonsense; handling the magic values requires even more complex special handling. The default behaviour of nulls usually matches how people want missing information to be treated.
The idea to put nullable columns in a seperate table only makes sense if all these columns are linked to a single subtype, so that they all are not nullable in the new table. And even then, the use is limited - the downside of splitting these columns to their seperate table is the introduction of outer joins in many queries. And the "special handling" is not avoided, since these outer joins throw the nulls right back in your face.


Absolutely agree. Magic values suck!

However, looking the table in the question I'd probably remove the NULL-ability on the cell phone numbers. Nullable VARCHAR often doesn't make sense. Particularly when they are entered in a classic GUI. How do you diffrentiate between a NULL phone number and a zero-length phone number?

As a side, nullable columns are often a consequence of a lazy or thoughtless developer-cum-DBA adding columns to existing tables, and they just don't know how to add the column with a usable default value for existing rows, then dropping the default.

And I agree with everyone else that the multiple identifier option should not be checked to get the question right. It's certainly not stated in BOL, and I don't think it's correct anyway. A technical ID and a business ID is very often a good idea.


Just because you're right doesn't mean everybody else is wrong.
Toreador
Toreador
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2837 Visits: 8084
Rune Bivrin (9/24/2010)
However, looking the table in the question I'd probably remove the NULL-ability on the cell phone numbers. Nullable VARCHAR often doesn't make sense. Particularly when they are entered in a classic GUI. How do you diffrentiate between a NULL phone number and a zero-length phone number?


Surely in this case, an empty string means that they don't have a cell phone, NULL means you don't know whether they have or not.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search