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 ««123»»

CASCADE - 2 Expand / Collapse
Author
Message
Posted Tuesday, March 26, 2013 7:26 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: 2 days ago @ 10:00 AM
Points: 3,189, Visits: 1,269
L' Eomot Inversé (3/26/2013)

And intensly I dislike the horrible violation of 1NF - code examples like this risk teaching really awful habits to inexperienced people who see them.


I'm very embarrassed to ask this but how does the code example violate 1NF?



Post #1435471
Posted Tuesday, March 26, 2013 7:53 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 3:14 PM
Points: 7,815, Visits: 9,564
kevin.l.williams (3/26/2013)
L' Eomot Inversé (3/26/2013)

And intensly I dislike the horrible violation of 1NF - code examples like this risk teaching really awful habits to inexperienced people who see them.


I'm very embarrassed to ask this but how does the code example violate 1NF?

Look at the construction of the field OrderDetailID in insertion code. You'll see that the orderdetailID (a single column) is constructed as an integer which when written out in decimal notation has the detail number within the order for which it is being created in the LS three digits and the order number in the more significant digits. This violates the principle that a row contains a single atomic value in each column, never multiple values in a single column - because here we have an order number and an order detail detail number both in the same column in each row.


Tom
Post #1435490
Posted Tuesday, March 26, 2013 7:57 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, October 29, 2014 1:18 PM
Points: 6,056, Visits: 8,342
I think you're reading a bit too much into the code used to create some sample data, Tom. I suspect ROn used this method to eaily visualize which rows belong together, but not with any other intention.

Of course, it is indeed bad practice to form values like this and store them as single column for real data. But for some sample code, I don't object to it.



Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #1435494
Posted Tuesday, March 26, 2013 8:02 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, October 27, 2014 6:49 AM
Points: 1,581, Visits: 1,859
Hugo Kornelis (3/26/2013)
I think you're reading a bit too much into the code used to create some sample data, Tom. I suspect Ron used this method to easily visualize which rows belong together, but not with any other intention.

Of course, it is indeed bad practice to form values like this and store them as single column for real data. But for some sample code, I don't object to it.


+1 - It's just a simple loop to generate some sample data.
Post #1435501
Posted Tuesday, March 26, 2013 8:14 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 7:30 AM
Points: 1,931, Visits: 1,446
Ez Pz. No got'cha in this question. Thanks!



Everything is awesome!
Post #1435510
Posted Tuesday, March 26, 2013 8:26 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: 2 days ago @ 10:00 AM
Points: 3,189, Visits: 1,269
L' Eomot Inversé (3/26/2013)
kevin.l.williams (3/26/2013)
L' Eomot Inversé (3/26/2013)

And intensly I dislike the horrible violation of 1NF - code examples like this risk teaching really awful habits to inexperienced people who see them.


I'm very embarrassed to ask this but how does the code example violate 1NF?

Look at the construction of the field OrderDetailID in insertion code. You'll see that the orderdetailID (a single column) is constructed as an integer which when written out in decimal notation has the detail number within the order for which it is being created in the LS three digits and the order number in the more significant digits. This violates the principle that a row contains a single atomic value in each column, never multiple values in a single column - because here we have an order number and an order detail detail number both in the same column in each row.


Thanks for explaining, I didn't notice this about the data.

It appears his example update on orderID points out why this is bad practice.



Post #1435527
Posted Tuesday, March 26, 2013 10:33 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Friday, October 24, 2014 12:43 PM
Points: 4,126, Visits: 3,428
Thanks for an easy one, Ron!
Post #1435608
Posted Tuesday, March 26, 2013 11:25 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 2:24 AM
Points: 1,926, Visits: 2,356
good question for the day


_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1435636
Posted Tuesday, March 26, 2013 12:07 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 2:04 PM
Points: 1,066, Visits: 3,138
Thanks for the question
Post #1435648
Posted Tuesday, March 26, 2013 12:27 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 3:14 PM
Points: 7,815, Visits: 9,564
Hugo Kornelis (3/26/2013)
I think you're reading a bit too much into the code used to create some sample data, Tom. I suspect ROn used this method to eaily visualize which rows belong together, but not with any other intention.

Oh, I don't for a moment imagine that Ron was intentionally breaking 1NF, or that he even noticed he was doing. And of course it's completely irrelevant to the question - any old table will do as long as it has a foreign key. That's why I said it was a good question apart from the strange wording of the answer options.

But I've often been hit by junionr developers seeing something and thinking "Oh, that's neat, perhaps I can use that" when the something they saw was absolutely terrible practice but acceptable in a particular context because it wasn't relevant there. So I try to avoid doing such things even when they are not relevant to thetask at hand, and believe that everyone should do so. Especially in contexts where they are likely to be seen by a lot of not particularly experienced people.


Tom
Post #1435655
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse