Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Best Practices for Database Design


Best Practices for Database Design

Author
Message
Scott Arendt
Scott Arendt
SSCarpal Tunnel
SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)

Group: General Forum Members
Points: 4048 Visits: 1702

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


Tatsu
Tatsu
Old Hand
Old Hand (302 reputation)Old Hand (302 reputation)Old Hand (302 reputation)Old Hand (302 reputation)Old Hand (302 reputation)Old Hand (302 reputation)Old Hand (302 reputation)Old Hand (302 reputation)

Group: General Forum Members
Points: 302 Visits: 307
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
Adam Machanic
Adam Machanic
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1141 Visits: 714
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
DanaH1976
DanaH1976
Valued Member
Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)

Group: General Forum Members
Points: 50 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
DanaH1976
DanaH1976
Valued Member
Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)

Group: General Forum Members
Points: 50 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
noeld
noeld
SSCertifiable
SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)

Group: General Forum Members
Points: 6320 Visits: 2048

>>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
Adam Machanic
Adam Machanic
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1141 Visits: 714
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
Robert Davis
Robert Davis
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1702 Visits: 1623
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 MVP
Database Engineer at BlueMountain Capital Management
Scott Arendt
Scott Arendt
SSCarpal Tunnel
SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)

Group: General Forum Members
Points: 4048 Visits: 1702

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


DCPeterson
DCPeterson
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1049 Visits: 432

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



*****************/
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search