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

Best Practices for Database Design Expand / Collapse
Author
Message
Posted Wednesday, April 6, 2005 7:08 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Thursday, July 24, 2014 8:03 AM
Points: 3,442, Visits: 1,473

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

Post #172383
Posted Wednesday, April 6, 2005 7:16 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, July 7, 2014 12:50 PM
Points: 292, Visits: 264
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

Bryant E. Byrd, BSSE MCDBA MCAD
Business Intelligence Administrator
MSBI Administration Blog
Post #172386
Posted Wednesday, April 6, 2005 7:17 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, July 18, 2014 7:02 AM
Points: 1,140, Visits: 701
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
SQL Server MVP
SQLblog.com: THE SQL Server Blog Spot on the Web
Post #172387
Posted Wednesday, April 6, 2005 7:33 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, April 6, 2006 2:42 PM
Points: 48, Visits: 1

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
Post #172393
Posted Wednesday, April 6, 2005 7:37 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, April 6, 2006 2:42 PM
Points: 48, Visits: 1

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
Post #172396
Posted Wednesday, April 6, 2005 7:47 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, May 6, 2014 5:51 AM
Points: 6,266, Visits: 2,028

>>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
Post #172401
Posted Wednesday, April 6, 2005 7:51 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, July 18, 2014 7:02 AM
Points: 1,140, Visits: 701
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
SQL Server MVP
SQLblog.com: THE SQL Server Blog Spot on the Web
Post #172404
Posted Wednesday, April 6, 2005 8:35 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, July 25, 2014 10:16 AM
Points: 1,612, Visits: 1,537
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
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring
Microsoft Certified Master: SQL Server 2008
Principal DBA: Outerwall, Inc.
Also available for consulting: SQL DBA Master
Post #172428
Posted Wednesday, April 6, 2005 8:38 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Thursday, July 24, 2014 8:03 AM
Points: 3,442, Visits: 1,473

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

Post #172429
Posted Wednesday, April 6, 2005 9:03 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 8:16 AM
Points: 1,035, Visits: 410

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



*****************/
Post #172436
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse