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 12»»

Strict Database Standards and Conventions Expand / Collapse
Author
Message
Posted Sunday, December 9, 2001 12:00 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, August 19, 2009 2:26 AM
Points: 5, Visits: 3
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/MFagerlund/datastandards.asp


Post #1878
Posted Friday, December 14, 2001 10:04 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 4:55 PM
Points: 31,278, Visits: 15,736
Interesting article. You have some good ideas, but I'm not sure of their benefits. The large prefixes can create some confusion, especially with developers accessing the tables. For DBAs, I think this will help greatly, but for a large development team, especially one with new developers, this might become very confusion.

One other note, the use of Underscores seems to swing in and out of favor. With the release of .NET, they are out of favor and SQL is supposed to be moving in that direction.

Nice article and well written.

Steve Jones
steve@dkranch.net







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #24857
Posted Friday, December 14, 2001 9:51 PM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: Moderators
Last Login: Monday, November 17, 2014 2:20 PM
Points: 6,800, Visits: 1,914
Hey Mattias,

I found it interesting as well, though I guess I'm going to fall into the opposition camp on this one! To me, this has pitfalls similar to Hungarian naming - too often you run into situations where the name doesnt fit but you're stuck with it, or you end up violating the convention for a 'special case'.

I can see where it might boost productivity in some cases - do you find that it does so enough to offset the increased time/effort it takes to enforce the convention?

Thanks for writing the article, good to get some different points of view on here! Hope we see more stuff from you in the future.


Andy


Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
Post #24858
Posted Saturday, December 15, 2001 4:47 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, August 19, 2009 2:26 AM
Points: 5, Visits: 3
quote:
but for a large development team, especially one with new developers, this might become very confusion.


Well, I've used the method with medium sized development teams (5 to 6 people), and it didn't cause a lot of cunfusion, that I can recall. I found that it was usually beneficial to new developers, because once they understood the module system, they could navigate the database much easier. Then again, most everything is confusing to the unexerienced developer.

quote:

One other note, the use of Underscores seems to swing in and out of favor. With the release of .NET, they are out of favor and SQL is supposed to be moving in that direction.



I personally find it easier to read with the underscores than it is without them. I guess it's a matter of taste.

quote:

Nice article and well written.



Thank you!

m




Post #24859
Posted Saturday, December 15, 2001 4:55 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, August 19, 2009 2:26 AM
Points: 5, Visits: 3
quote:

To me, this has pitfalls similar to Hungarian naming - too often you run into situations where the name doesnt fit but you're stuck with it


Hungarian notation does have severe limitations in database word, as pointed out in an execellent article somewhere on SQLServerCentra.com. However, I don't see that these problems are also inherent in this method.

The main problem with hungarian notation is that if you change the datatype of a column, you have to change the name of that column - and the odds of doing that once the database is in use is very slim indeed. However, the only time you need to change the name of a column using this method is when the column moves from one table to another table - and when that happes, you have a lot of other maintenance work to do anyway. Chaning the name isn't that much of a deal, since you have to re-visit every piece of code or script that accesses that part of the database anyway.

quote:

, or you end up violating the convention for a 'special case'.


Well, that's a matter of discipline. I find that with experience, developers get more disciplined. No method will work well if you don't practice discipline, but perhaps a strict method will suffer more.

quote:
I can see where it might boost productivity in some cases - do you find that it does so enough to offset the increased time/effort it takes to enforce the convention?[quote]

Oh, yes, certainly. Whenever I get called on to work on a system where the method is not used, I find I miss it dearly. Maybe that's because I'm so used to it, it's hard to tell, but I find great benefits.

[quote]Thanks for writing the article, good to get some different points of view on here! Hope we see more stuff from you in the future.


Thanks, and thanks for the feedback! That's what makes it worth the effort. Thanks also to Steve Jones for his feedback, and Brian for inspiring me to write the article ;)

mattias


Edited by - mattias on 12/15/2001 04:56:50 AM



Post #24860
Posted Monday, December 17, 2001 3:38 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, May 21, 2008 4:05 AM
Points: 291, Visits: 32
Nice, clear article - I think if I come across this type of system in the future I'll easily recognise it now. Not sure that I would choose to implement it on small/medium sized dbs, but on dbs with a massive number of tables i can see it could be a useful way of administering a modularised structure.

My question regards foreign keys: what if two tables have a foriegn key to the same field on a third table - how is this column then named?

Second question is to Steve: saw in your question that there is an issue with underscores and .NET - am not really familiar with .NET yet, so could you explain this a bit for me.

Paul Ibison
Paul.Ibison@btinternet.com



Paul Ibison
Paul.Ibison@replicationanswers.com
Post #24861
Posted Monday, December 17, 2001 6:13 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, August 19, 2009 2:26 AM
Points: 5, Visits: 3
quote:

My question regards foreign keys: what if two tables have a foriegn key to the same field on a third table - how is this column then named?



I'm not quite sure I unserstand you here, but; Foreign keys are always named using their "domestic" name - the name they have in their home table.

If table T0201_AGENT and T0301_DEPARTMENT both have foreign keys that refer to the table T0101_CONTACT, then both (foreign key) columns would be named T0101_CONTACT_ID. Since they're on separate tables, it shouldn't pose a problem.

Hope that answered your question, if it didn't, please elaborate, and I'll give it another shot.
m


Edited by - mattias on 12/17/2001 06:15:53 AM



Post #24862
Posted Monday, December 17, 2001 8:20 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 4:55 PM
Points: 31,278, Visits: 15,736
.NET is the new direction for all MS apps, supposedly T-SQL. This will mean that T-SQL will support inheritence, encapsultion, etc. Not sure how that will work, but supposedly T-SQL will become a .NET server and you will be able to code in any .NET languange inside SQL. Again, I can't really imagine how this will work, but that's what I've heard from a few peoplea t MS.

In terms of underscores, you can use them, but MS is recommending you do not for .NTE languages (VB.NET, c#.net).

Steve Jones
steve@dkranch.net







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #24863
Posted Tuesday, December 18, 2001 5:09 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, May 21, 2008 4:05 AM
Points: 291, Visits: 32
Thanks for the answers guys.

Steve: the direction you mention for TSQl sounds exciting, although presumably it'll be a couple of years before this comes about.

Mattias: my FK question really concerns a column on a table which is a foreign key in more than one instance. It may be a foreign key on it's own to table A and part of a combined FK to table B. Admittedly this is a slightly unusual situation, but how would the naming cope with this? (another such problematic 'exception' would be self-joins).

Paul Ibison
Paul.Ibison@btinternet.com



Paul Ibison
Paul.Ibison@replicationanswers.com
Post #24864
Posted Friday, December 21, 2001 2:20 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, August 19, 2009 2:26 AM
Points: 5, Visits: 3
quote:

Mattias: my FK question really concerns a column on a table which is a foreign key in more than one instance. It may be a foreign key on it's own to table A and part of a combined FK to table B. Admittedly this is a slightly unusual situation, but how would the naming cope with this?


Well, if it's a partial foreign key, that is, one of several columns that together form a foreign key, then each column would be named as the coresponding columns are named in the "mother" table.

quote:

(another such problematic 'exception' would be self-joins).



In the case of a self-join, I add a descriptive text to the name, after the regular column name. If T0101_CONTACT has a reference to a "mother" and a "father", these columns would be named T0101_CONTACT_mother and T0101_CONTACT_father.

This _is_ a problem, no doubt, but the problem isn't considerably smaller with any other method. The one reason it may be a slighly bigger problem using the strict conventions is that you really expect the column to have a specific name - but it doesn't, because that would cause several columns with the same name. Using no naming conventions, that particular problem doesn't occur, because you simply don't know what the name of the field would be, unless you looked it up.

m



Edited by - mattias on 12/21/2001 2:22:01 PM



Post #24865
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse