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


An Introduction to Database Models


An Introduction to Database Models

Author
Message
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

Business Rules in the DB

The only rules that should be in the DB are the ones that are used to enforce data integrity. Triggers are often used to enforce data integrity when the database was designed wrong in the first place. If you have a trigger to perform an action when an event occurs, that is business logic. If you are using a trigger to ensure that if a certain flag is set on a row then another table has to have certain rows in it then that is data integrity. A simple criteria for trigger necessity might be this: If I can get a valid report out of the database without the trigger to ensure that the data is good, then it is probably a business-rule trigger and probably should not be in the database.

Just because the DB server can do a thing, it does not follow that you should make it do that thing.

Serializing database access

Yeah, I guess that is what I was thinking. You might be able to scale it out some if the data access components talked to each other too. This would probably be way too much overhead for a high transaction load though and you should probably just go with the polling solution. There are supposed to be a feature coming that will notify data sets when the data has changed I think but I could be way wrong on that.



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
Serialization, IMO, defeats the purpose of having a DBMS and brings us back to flat-file land. Part of the definition of a DBMS is that it's shared - many processes can talk to the DBMS at once. Serializing in essence is the same as putting the database into single-user mode and then letting the app hit it. How can that ever perform well?

--
Adam Machanic
SQL Server MVP
SQLblog.com: THE SQL Server Blog Spot on the Web
thormj
thormj
SSC-Enthusiastic
SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)

Group: General Forum Members
Points: 107 Visits: 1

A simple criteria for trigger necessity might be this: If I can get a valid report out of the database without the trigger to ensure that the data is good, then it is probably a business-rule trigger and probably should not be in the database.

Just because the DB server can do a thing, it does not follow that you should make it do that thing.

Hrm. I am using .NET, and most of the "compute intensive" and otherwise funky stuff (getting data from ancient systems) are running as a windows Service. -- This provides some validation data and alarms for older systems.

More advanced systems email us their problems; I was planning to use SQLMail on a 5 minute interval to import the alarms into a table.

With the questions of:

  1. Where will the object run (Service, Webserver/ice)?
  2. How will the object be notified that there are new tasks (polling? MSMQ? funky...) ?

I thought it would be better to put the "Rule Matching" logic into triggers and stored procedures; these would either call a procedure (mainly for emailing reports), or run a program (using xp_cmdshell). I will keep these "Event Triggers" separate from the "Integrity Triggers" (it looks like MSSQL can handle multiple trigger procedures... I suppose I'll have to figure out how to order them...)

On a related note, does anyone have a suggestion for a reporting tool that can output plain text? I can't seem to get that with either CR or MSReports...

TIA,
Thor





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

Thor:

DataDynamics ActiveReports can. I've used it quite extensively and IMO it's a pretty solid product. Better automation capabilities than CR and very cheap too. I haven't tried MS Reporting Services yet though, so I can't compare it.



--
Adam Machanic
SQL Server MVP
SQLblog.com: THE SQL Server Blog Spot on the Web
cs_troyk
cs_troyk
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1529 Visits: 965

I would like to commend Mr. Hedgate on an insightful and articulate critique of this article. The two links in his response provide excellent resource for taking a step towards a more fundamental understanding of the Relation Model.

I would urge those with an interest in the content to explore the rest of the dbdebunk site. I find Pascal's critiques of XML and Denormalization especially thought-provoking.

TroyK





Steve Jones
Steve Jones
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: Administrators
Points: 36346 Visits: 18752

I haven't had a lot of experience with OODBMS's so I can't comment from experience, but it seemed that they were mainly built to "simplify" db access for developers. Rather than having the developer have to understand and store data in multiple places (and retrieve it), it's stored in an object structure and more easily retrieved.

They also have different indexing structures. I too am not sold that they really work well, but I have seen some implementations (can't remember them now, think NASDAQ or Chicago BOT did one, and the performance was far above their old system. I'm not sure this says their approach was better or maybe the previous approach was horrible, but it was interesting.

Good overview Frank!



Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Frank Kalis
Frank Kalis
SSCertifiable
SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)

Group: General Forum Members
Points: 5971 Visits: 289

First of all, sorry for being that late on my reply. I was voluntarily completely offline last week.

Glad to see I caused some discussion.

Steve, is it possible to automatically subscribe an author to the thread on his article?



--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/
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

I thought about the serialization thing a little more and figured out a way to handle the situation where notification needs to be sent when changes are made to the database. There are two ways to handle this.

Message Queuing - This is exactly the sort of thing queuing is for. It guarantees that you message gets to all the destinations so you can put a message on the queue and the queue reader for the database change with post the update to the database and the email queue reader will shoot an email to the appropriate recipients.

Email Service - Providing an email service in your business layer would allow you to make the database update and then fire off an email through an email service you create. The queuing option is probably more reliable but may involve a learnign curve.

There are probable other options out there but I want to mention that email is inherently unreliable unless you have a mechanism for ensuring delivery and SQL Server can't do that very well. This does not take into account the reliability of SQL Mail. I have had enormous amounts of trouble with SQL Mail and I highly recommend that it not be used for applications. It is fairly stable if you're just sending alerts from SQL Server and SQL Agent but relying on it for business needs is not a good idea. If you have the capability to monitor the event log, I would not even use SQL Mail at all; just make sure that critical alerts are posting to the Windows Event Log.



Bryant E. Byrd, BSSE MCDBA MCAD
Business Intelligence Administrator
MSBI Administration Blog
thormj
thormj
SSC-Enthusiastic
SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)

Group: General Forum Members
Points: 107 Visits: 1

This does not take into account the reliability of SQL Mail. I have had enormous amounts of trouble with SQL Mail and I highly recommend that it not be used for applications. It is fairly stable if you're just sending alerts from SQL Server and SQL Agent but relying on it for business needs is not a good idea.

Ok... now I'm beginning to worry:
Alarm messages filter into a public folder on our Exchange server, and these need to get into SQL somehow.

I was going to write a stored procedure that did the xp_OpenMail, insert into foo from selectunread, xp_CloseMail bit, but if sqlmail's reputation holds true, it sounds like I'm in trouble

Any suggestions?
(I do like your suggestion for using MSMQ for reliable msg delivery and will use it when we get around to the notification part of the project)





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

Where do the alarm messages come from? Would there be a way to dump them to a flat file or something? The .NET framework provides a file/folder listener that can detect changes to a file and provide events to an application or service so the events can be handled.

The main problem I have seen with SQL Mail has been with sending emails. I have never used it to read mail before. The problems tend to occur in MAPI sessions and to get a session to quit, you need to restart the SQL Server services. If your system is on a cluster, I would forget even attempting to use SQL Mail. Microsoft does not support SQL Mail on a cluster due the the MAPI sessions inability to fail over with the SQL Server. I have seen other problems with MAPI on clusters that may or may not be related to clustering itself.

My suggestion is to look at the problem your software is intended to solve from end-to-end and see where you might be able to streamline some things that will make future development faster and more able to provide better service. This might make the overall project timeline shorter even if the current phase goes into overtime.



Bryant E. Byrd, BSSE MCDBA MCAD
Business Intelligence Administrator
MSBI Administration Blog
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