Best Practices for Database Design
-
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/jgonzalez/bestpracticesfordatabasedesign.asp
-
Why prefix views with a 'v'? Tables and views are identical as far as clients are concerned -- there is no reason to muddle your data model with that prefix.
Also, please define "reference". At what point does a "reference" table cease being for a reference? Can a non-reference table become a reference? Will you rename the tables at those times? You mention tables of two columns being reference tables. Can a three-column table ever be a reference? Four? What's the cutoff?
--
Adam Machanic
whoisactive -
View:
Well,
a view simply isn't identical to a table, client or not,
and if you try to insert something into a view the error that will pop up will clearly
explain the difference.
so better mark them with a prefix so you can't mix it up ;o)
Reference Table:
I would call a table a reference table if it is i.e. a table which contains a n-m relation.
it just consists of foreign key references and maybe also some content fields,
but the main content is the n to m relation.
In my opinion the number of columns is irrelevant, but the relation by which the data
contained in the table is linked gives the context...
___
Markus Engelhardt
Web and Database Development
MCP
-
I prefix all of my views with 'vw' and make the same requirement of the developers where I work. The main reason you would want to add a prefix is to avoid problems where developers try to give objects the same name. For example, if you have a table named 'promo_ref', you can't have a view named 'promo_ref' (unless you give it a different owner).
I'd hardly call adding 'v' or 'vw' to the name of views "muddling your data model". That's like saying that a penny is muddling Bill Gates' bank account.
What I don't do is use underscores in object names. It's not a rule, just a preference. Underscores slow me down when I'm typing.
My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url] -
For enterprise databases, an elementarian (role) should be appointed to manage a library of abbreviations that form the elements of database object names. The creation of a stable element library should occur in parallel with conceptual/logical modeling and before physical design.
Jeb Bushell
P.S. My favorite element abbreviation is rvnu for revenue
-
Naming standards are very good things. Common abbreviations are very good things.
Very good things can, however, be taken to silly extremes. We've got a logical modeling team tasked with, along with maintaining an enterprise logical model, establishing naming conventions. Unfortunately, these people work in the ethereal world of logical modeling, exclusively. They don't have to write code against the models they create, so naming every single table in a database Policy...* is not a problem for them even when we're talking about a couple of hundred tables, all inside a database named Policy. Establishing incomprehensible abbreviations like 'ddltbl' for deductible (do abbreviations generally add letters? note the new 'l') doesn't seem to slow down their work at all. The rest of us, dba's and developers, have been driven insane trying to get them to use an abbreviation like 'org' for organization instead of 'orgntzn'. I have, on more than one occasion, brought a dictionary over to their desk and pointed at common abbreviations defined with along with a word to no avail.
Don't get me wrong. I am in favor of practing a common approach. Just make sure the common approach makes sense.
Oh, and underscores in object names suck.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
I agree with some of the ideas in the article, but I hate underscores in table field names. Capitalizing each word within the name is just as readable and much easier to type (i.e., GrossAmountPaid).
And what's wrong with using a prefix and naming a table tblInvoices?
I try to avoid abbreviations unless their meaning is just as clear as the long version would have been.
Dana
Connecticut, USA
Dana -
By the way, the title of this article is misleading. I expected to see something about natural versus artificial keys, unique constraints, resolving sub-type roll-ups, logical arcs, denormalization by design, defining the cluster index as part of table design, establishing clustering strategies as part of database design. You know, database design. Instead it was a decent piece of work on establishing standards in naming. Not bad, just a surprise.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
You make several good points. I like a prefix on views so I know where to look in the database for the view/table. On some projects, I have also used tbl for tables, but this varies.
A lot of places require the use of stored procedures for data access, and use prefixes of sps_, spi_, spu_ and spd_ which potentially clashes with system procedures in SQL Server. Also, many times the stored procedure does an update or insert depending on what is necessary so spi_ and spu_ become confusing.
-
This is no slight against J.D. but I have read more than enough naming convention articles
To J.D.'s credit, he does present some interesting new ideas but this is one of those "religious" areas that could lead to centuries of discussion. Can we get a forum area[font="Tahoma"]Bryant E. Byrd, BSSE MCDBA MCAD[/font]
Business Intelligence Administrator
MSBI Administration Blog -
You can argue all day about naming conventions. But I think that the main point is too decide on a convention, document it, and enforce it.
Regards,
Scott
-
Exactly Scott! It's OK to present new ideas and use other people's ideas to improve existing standards as well. I don't think there are many people in this community who would argue that standards are not beneficial. It is important to communicate new ideas but I don't think it warrants article status until we can identify a good set of standards that at least 80% of the active community can agree on. I don't see that happening
[font="Tahoma"]Bryant E. Byrd, BSSE MCDBA MCAD[/font]
Business Intelligence Administrator
MSBI Administration Blog -
INSTEAD OF triggers are there to support view updatability.
So any table in an n-m relationship is a reference table? Does every table in your database with that kind of relationship have that prefix/suffix?
--
Adam Machanic
whoisactive -
Regarding the post "A lot of places require the use of stored procedures for data access, and use prefixes of sps_, spi_, spu_ and spd_ which potentially clashes with system procedures in SQL Server. Also, many times the stored procedure does an update or insert depending on what is necessary so spi_ and spu_ become confusing."
I use the prefixes upd_, ins_, and del_ for stored procedures that perform update functions. To get around the conflict with system procedures that start with sp_, I use sel_ for simple selects. This is very clear even to a non-programmer, and makes it easy to find procedures by their function.
Now if only Enterprise Manager would let us save sprocs in folders so we didn't have to scroll up and down a list with thousands of entries!
Dana
Connecticut, USA
Dana -
For reference tables, I use a suffix of "Lookup". Had I to do it over again, I would have used a special prefix instead so that the lookup tables would sort as a group. However, the suffix does make the function of the table very clear.
Almost all of our lookup tables have two fields (three at the most), usually just a key and a description. If the table has any function other than a lookup, it's not considered a reference table.
Dana
Connecticut, USA
Dana
Viewing 15 posts - 1 through 15 (of 145 total)
You must be logged in to reply to this topic. Login to reply