Best Practices for Database Design
-
>>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.<<
>>INSTEAD OF triggers are there to support view updatability.<<
Assuming Permitions are granted, some views can be updatable even without instead of triggers :
From BOL
a view does not use an INSTEAD OF trigger or is not an updatable partitioned view, it can still be updatable provided that:
- The view contains at least one table in the FROM clause of the view definition; the view cannot be based solely on an expression.
- No aggregate functions (AVG, COUNT, SUM, MIN, MAX, GROUPING, STDEV, STDEVP, VAR, VARP) or GROUP BY, UNION, DISTINCT, or TOP clauses are used in the select list. However, aggregate functions can be used within a subquery defined in the FROM clause provided that the derived values generated by the aggregate functions are not modified.
- No derived columns are used in the select list. Derived columns are result set columns formed by anything other than a simple column reference
[edit:] And I should add that the update statement should affect columns on a single base table
* Noel -
Again: Define "lookup." What is the function of a lookup table that makes it special (i.e. deserving of a different prefix / suffix than other objects)? Can you specify immutable rules for when to use -- and not to use -- this prefix?
--
Adam Machanic
whoisactive -
One prefix that I want to throw into the mix is "NFR_". Yes, I know it has an undescore which I said I hated earlier. Any table that we do not want to be (merge) replicated are named with a prefix of NFR_ (not for replication) so that they can easily be excluded from replication.
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] -
Bryant, you misunderstood what I meant. I am suggesting that you adopt a standard for your organization and stick with it. It is better than not having a standard. To me it is more important that I can expect certain things from the developers that I work with. Whether or not those standards are accepted by the majority of the SQL Server community is not as important to me.
Regards,
Scott
-
True, views are not IDENTICAL in their behavior when it comes to DML operations (this is one of the major problems with SQL but that is another discussion.) However, according to relational theory the response to a query (a view) yields another relation (or more precisely a relation variable, or relvar) That the result is not stored permanently is immaterial, and in fact if your base relvars are designed correctly and your query is correct there is no reason that the view can't be inserted or updated.
As for reference tables, I dislike the term, along with all its cousins. There is no sufficiently precise definition to be useful. How does a table contain a n-m relation(ship)? Tables don't contain relationships, they contain propositions (assertions of fact) certain data within a table may reference data in another table, but the relationship is not contained in either table per se. Besides what I think you are talking about is what is frequently called a many to many lookup table which is not what most people mean when they use the term "reference table." That term is normally used to denote some sort of reference data that doesn't change frequently, or which is not generally modifiable by end users i.e. "State/Province", "Country", "StatusCode", etc... So it makes sense, in a limited way to speak of reference data, but not really reference tables, because the tables that hold the reference data do not act any differently than any other table and are certainly not subject to any special design considerations/exemptions at the logical level.
That you use the term "reference table" to denote a m-n lookup table while most others use it to describe something else completely is yet another bit of anecdotal evidence against adopting that terminology.
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
-
Thanks for the feedback. This was my first article and by no means am I an author. The article name is a bit misleading. I didn't realize it until it was published. It really should have read Best Practices for Naming Databases, Tables, etc...
The purpose of this article wasn't to say you should name your tables or views using 'v'. It was meant to say, use a naming convention and stick to it. Personally, I like using 'v' for views and keeping tables without a prefix. From a developer standpoint, it makes it much easier to find tables and they will always appear together when sorted by name. I use capital letters in my stored procedures as I find them easy to read. It's just a habit I have. Again, at a glance I can tell just by the name whether it's a table, view or a stored procedures.
I've been out for a few days so I really haven't had time to view all of the comments, but once again, thanks for the feedback.
J.D. Gonzalez
-
Actually I was agreeing with you. I should have started a new paragraph when I started expanding on my earlier argument that we don't need any more articles on examples of standards.
[font="Tahoma"]Bryant E. Byrd, BSSE MCDBA MCAD[/font]
Business Intelligence Administrator
MSBI Administration Blog -
This definitely deserves it's own forum. Each company finds it's own naming convention, and the fact that everyone uses it is more important than what they chose to use.
Personally, I like to call the first table "TableA", second "TableB" and so forth. That way no one can follow my code.
(Just kidding)
Student of SQL and Golf, Master of Neither -
Actually, I prefer to keep camel-hump notation limited to classes and and variable/functions. using underscores for database names is how I tell them apart.
-
Personally, I like to call the first table "TableA", second "TableB" and so forth. That way no one can follow my code.
That's one way to ensure job security!
-
Anyone ever look inside MS GreatPlains eEnterprise? How does MS name its tables?
-Isaiah -
It's been a long time since I looked at Great Plains and that was before MS bought them. Two points: 1) I wouldn't be surprised to see a bunch of "legacy" naming standards still in the database today, and 2) I have never looked to MS for guidance in either database design or naming standards. One look at the SQL Server system tables and you know that nobody at MS knows much about good database design, or if someone there does know anything, they aren't listened to by those in charge...
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
-
Please do not use MS as an example of how to architect anything -- the overall quality is improving, but historically there have been plenty of mistakes. VB, anyone? [G, D, and R]
Seriously, though, check out the new AdventureWorks database that ships with SQL Server 2005. They've gotten the naming scheme quite a bit closer to industry standards such as ISO-11179 (which deals with how to name Metadata). You'll note the lack of strange prefixes or suffixes, and that column names are descriptive of the attribute they model and lack data type descriptors or codes in most cases (IDENTITYs being one exception) -- this means that the online catalog provides that information, rather than the name itself. And that's a good thing, IMO. Some examples:
The table for departments is called Department. Not Department_Ref or Department_Lckp. The table is a collection of departments, and the fact that this data may change frequently or infrequently has no affect on queries. The client querying wants information on departments; that client is not concerned with how or when the data is updated!
Similarly, take the column Gender in the Employee table. This column is an NCHAR(1). So it's a code. But notice the lack of _Code or any other suffix. The client doesn't need that data -- all it needs to know is, it's getting data about the employee's gender.
One thing that does bother me about AdventureWorks is that all of the tables are named using singular rather than plural words. Since tables represent sets of entities, and sets are containers for "objects" (rather than an "object"), I believe that table and view names should be plural.
--
Adam Machanic
whoisactive -
I was being sarcastic... GreatPlains is a disaster to understand!
I think all tables and there attributes should be singular, not plural.
Customers.FirstNames???? NO
Customer.FirstName
-Isaiah -
Tables and views should be plural. Attributes should be singular.
Tables and views represent a set of entities, not a single entity (unless the table is a singleton, in which case perhaps this doesn't apply). Attributes represent a single scalar value (First Normal Form, anyone?)
Note, this is in line with what ISO-11179 says.
--
Adam Machanic
whoisactive
Viewing 15 posts - 16 through 30 (of 145 total)
You must be logged in to reply to this topic. Login to reply