Best Practices for Database Design

Question

Best Practices for Database Design

Viewing 11 posts - 136 through 145 (of 145 total)

You must be logged in to reply to this topic. Login to reply

Tom Garth SSCertifiable Points: 6193 More actions · Answer 1

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[/url]

"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

SqlNightOwl SSCrazy Points: 2331 More actions · Answer 2

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

Tom Garth SSCertifiable Points: 6193 More actions · Answer 3

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[/url]

"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

SqlNightOwl SSCrazy Points: 2331 More actions · Answer 4

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

Jeff Moden SSC Guru Points: 1003861 More actions · Answer 5

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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

sjsubscribe SSCrazy Points: 2080 More actions · Answer 6

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.

I have to agree with Paul Hunter here. Filling the forum with naming convention nonsense keeps real discussions about good database design away. If the author was particular about a certain style, then call it that instead of "good database design". As it stands now, naming conventions as described are neither good, database related, nor well designed. At best it's one solution that probably worked in the author's domain of experience.

As it is we already have enough abstractions for data and application models to layer them anyway we like to suit a particular database design. I don't see any value in imposing yet another spurious layer over already established practices and then call it good database design. Naming conventions have to evolve to suit the problem, not imposed.

Jeff Moden SSC Guru Points: 1003861 More actions · Answer 7

Ummm... you guys are getting awfully serious about this... you do realize that the article was written and first posted about 3 years ago, huh? 😉

--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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeremy Giaco Mr or Mrs. 500 Points: 537 More actions · Answer 8

Im pretty OCD about the naming conventions that I use, but as long as you have something, it really doesnt matter what they are. Granted, some seem to work better than others for me, but everyone's different. I do feel that it is important to sit down with your respective teams, and nail down a consistent pattern though. It is very nice to walk into a company, read the naming conventions document, and pretty easily guess at how to query the data based on the tables names without digging into each object to identify the columns.

"What was the name of the customer that had orderid 23?...oh,well that would be Select CustomerNm From Customer c Inner Join Order o on c.CustomerId = o.CustomerId Where o.OrderId = 23, of course!" (not that I would format it like a sentence or totally rely on the naming, but you get the idea).

When you get to the point when you have standards for conventions in general, the code pretty well writes itself.

SqlNightOwl SSCrazy Points: 2331 More actions · Answer 9

Jeremy Giaco
It is very nice to walk into a company, read the naming conventions document, and pretty easily guess at how to query the data based on the tables names without digging into each object to identify the columns.

Very well stated Jeremy. I work with a horribly designed database but the naming convention is solid and helps describe the database and it's relationships. The Acct table has a column named MjAcctTypCd (major account type code) and the lookup code table is named MjAcctTyp and is consistent for any code table. The only exception (which is understandable) is that the CurrAcctStausCd comes from the AcctStatus table.

--Paul Hunter

SqlNightOwl SSCrazy Points: 2331 More actions · Answer 10

Hey Jeff -- a good thread is a good thread. 😉

--Paul Hunter

Jeff Moden SSC Guru Points: 1003861 More actions · Answer 11

paulhunter (6/27/2008)
Hey Jeff -- a good thread is a good thread. 😉

No doubt about that... 🙂

--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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)