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

Best Practices for Database Design Expand / Collapse
Author
Message
Posted Friday, June 20, 2008 9:02 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, September 15, 2014 3:43 PM
Points: 266, Visits: 2,593
Here's a naming option some people are really going to hate:

Mostly use mixed case to name your tables. Examples:
. FacilityPerson
. AbuseReferral
. StateSawFacility

However, use underscores occasionally to help make a table easier to read, especially when there are several tables with very similar names. For example, I chose NOT to do these names in a database that generally follows the above convention.
. LegalConnectRisk
. LegalConnectLog
. LegalConnectAbsRef

With the beginning part the same for all three tables and the name being so long (and several other tables starting with the text 'Legal', I find the following easier to read:
. LegalConnect_Risk
. LegalConnect_Log
. LegalConnect_AbsRef

I don't mind "mixing" conventions when it makes sense. (It's not really mixing. It's just a more complicated convention.) And this does to me. Note: over 95% of the tables in my databases do *not* have underscores.
Post #520798
Posted Friday, June 20, 2008 10:46 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, January 3, 2012 1:48 PM
Points: 1, Visits: 17
Nice article with very good points.
I would like to add that using table prefixes allows us to have a subset of tables show up together in table view.

Also, the audit fields I use are more cumbersome at times but perhaps more complete:

add_datetime
added_by
add_pgm
edit_datetime
edited_by
edit_pgm

These fields have saved countless hours in debugging.
Post #520889
Posted Friday, June 20, 2008 12:12 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Tuesday, August 5, 2014 12:28 PM
Points: 4,665, Visits: 635
Wow, Adam and DC should did earn a lot of points during this discussion...
Post #520952
Posted Friday, June 20, 2008 1:50 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, December 20, 2012 1:03 PM
Points: 265, Visits: 589
Adam Machanic (4/6/2005)
Tables and views should be plural. Attributes should be singular.


How does this rule apply to a table of sheep that a stud farm maintains?
Post #521029
Posted Friday, June 20, 2008 3:53 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, March 24, 2011 3:20 PM
Points: 277, Visits: 52
Good article.
I use VIEW_ to prefix all views.
I completely agree on naming fields EXACTLY the same from one table to another (if they refer to the same data). I don't even put suffixes on the field name - ID exists as ID in many of my tables.

I also believe ERD's are in order to define how all your tables are related, with a repository where the ERD's are stored. I know this may take a little extra time, but as with everything else in this field, get in the habit and it becomes second nature. ERD's provide your successors/co-programmers some help in decrypting your naming conventions/database decisions when you're not around. I haven't seen any forums or articles about ERD's since I subscribed to sqlservercentral.com.
Post #521090
Posted Saturday, June 21, 2008 10:06 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, February 4, 2011 7:20 AM
Points: 977, Visits: 1,499
A very nice article. Straight forward and practical advice. It's good to see these practices written about regularly, and I always like hearing the many various preferences that others express.

My own preference for stored procedure naming is to reference the object then the action. I prefer to have them grouped by object rather than action, particularly when working in a database with hundreds of them.

So instead of...
usr_getMenus

being found with all of the other 'gets', I would look for it with all of the 'Menus' procedures...
usr_MenusDelete
usr_MenusGet
usr_MenusInsert
usr_MenusUpdate


Tom Garth
Vertical Solutions

"There are three kinds of men. The one that learns by reading. The few who learn by observation. The rest of them have to pee on the electric fence for themselves." -- Will Rogers
Post #521254
Posted Saturday, June 21, 2008 11:55 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, July 1, 2014 9:55 AM
Points: 201, Visits: 404
What a let down. I was expecting an article on good database design practices. Instead we're treated to another diatribe on naming conventions. About the only thing I agreed with in the article was about granting access to the underlying tables. The rest of the article was one persons opinion that can nowhere near what I'd consider a best practice.

--Paul Hunter
Post #521284
Posted Saturday, June 21, 2008 12:28 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, February 4, 2011 7:20 AM
Points: 977, Visits: 1,499
paulhunter (6/21/2008)
What a let down. I was expecting an article on good database design practices. Instead we're treated to another diatribe on naming conventions. About the only thing I agreed with in the article was about granting access to the underlying tables. The rest of the article was one persons opinion that can nowhere near what I'd consider a best practice.


I didn't hear a diatribe. The author wasn't suggesting that anybody adopt his conventions. He was writing about the importance of having consistent conventions and practices within a single organization.

Yes, this is common knowledge among many of us, but it's important to have it restated occasionally, because there are many people just entering the field who may not have given it a thought yet. For them, the article and discussion make really good reading.


Tom Garth
Vertical Solutions

"There are three kinds of men. The one that learns by reading. The few who learn by observation. The rest of them have to pee on the electric fence for themselves." -- Will Rogers
Post #521291
Posted Saturday, June 21, 2008 1:03 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, July 1, 2014 9:55 AM
Points: 201, Visits: 404
I'm fine with a discussion on naming conventions. I have one that I use and have shared with many. My objection is that the title of the article leads me to believe I'd be reading an article on "Best Practices for Database Design". While naming conventions are important, they have less to do with design re Codd & Date and more to do with preference. Using prefix/suffix mnemonics for objects, a discussion of underscores, abbreviations or singular vs plural object names has very little to do with normalization, data structure, when to/not to use triggers and a whole host of good design practices.

For example, take singular vs plural in table names. A table containing customer data could be named customer for a single instance of the data or customers as it represents a collection of customer records. If you go with the plural form then, for consistency sake, your CRUD procedures wrapping the table would give you Customers_ins (or ins_Customers), select, delete, update maybe a save (sometimes called upsert). Now the question comes; if I'm only affecting one Customer with any of those actions then why is the base name plural?

As you can see, you've departed from design and gotten into preference. Both are perfectly valid options with rational arguments on either side. Neither should be called a "best practice" because neither is. They are both "common practices".

If the author want's to bring both points of view forward on "common practices" for discussion then good. If, as you state, he's bringing this forward so that people who haven't given serious consideration to this topic may begin, then that's good but, don't tell half the story and and say it's a "best practice".


--Paul Hunter
Post #521300
Posted Saturday, June 21, 2008 2:56 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Yesterday @ 9:16 PM
Points: 37,102, Visits: 31,655
Heh... I'll agree that the author had a naming convention problem with choosing the title of the article... ;)

I've also found that a great many "personal preferences" can really gum up the works unless they have a real and practical basis for use.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #521328
« Prev Topic | Next Topic »

Add to briefcase «««1112131415»»

Permissions Expand / Collapse