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