Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12345»»»

Design Expand / Collapse
Author
Message
Posted Thursday, September 23, 2010 8:35 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 8:37 AM
Points: 3,905, Visits: 5,079
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”
Post #992437
Posted Thursday, September 23, 2010 8:43 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, June 16, 2014 9:38 AM
Points: 2,163, Visits: 2,189
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.)
Post #992440
Posted Thursday, September 23, 2010 11:40 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:13 PM
Points: 5,967, Visits: 8,221
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
Post #992489
Posted Thursday, September 23, 2010 11:57 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, June 16, 2014 9:38 AM
Points: 2,163, Visits: 2,189
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.
Post #992493
Posted Thursday, September 23, 2010 11:58 PM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Sunday, June 29, 2014 11:26 PM
Points: 1,481, Visits: 1,960
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

Post #992494
Posted Friday, September 24, 2010 12:54 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:13 PM
Points: 5,967, Visits: 8,221
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
Post #992513
Posted Friday, September 24, 2010 1:33 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, March 22, 2012 1:50 AM
Points: 15, Visits: 51
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...
Post #992527
Posted Friday, September 24, 2010 2:31 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, October 22, 2013 6:47 AM
Points: 426, 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
Post #992551
Posted Friday, September 24, 2010 2:42 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 4:24 AM
Points: 3,011, Visits: 909
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.
Post #992556
Posted Friday, September 24, 2010 2:58 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 8:42 AM
Points: 1,736, Visits: 6,335
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.
Post #992561
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse