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

An Introduction to Database Models Expand / Collapse
Author
Message
Posted Thursday, February 12, 2004 9:42 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, November 10, 2014 7:17 AM
Points: 295, Visits: 284

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
Post #100064
Posted Thursday, February 12, 2004 10:07 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, September 25, 2014 9:01 PM
Points: 1,113, Visits: 705
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
Post #100076
Posted Thursday, February 12, 2004 10:58 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, September 6, 2005 10:53 AM
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




Post #100081
Posted Thursday, February 12, 2004 11:03 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, September 25, 2014 9:01 PM
Points: 1,113, Visits: 705

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
Post #100083
Posted Thursday, February 12, 2004 3:09 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 12:33 PM
Points: 1,339, Visits: 801

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




Post #100156
Posted Friday, February 13, 2004 7:33 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 4:16 PM
Points: 31,284, Visits: 15,748

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
Post #100363
Posted Sunday, February 15, 2004 3:12 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, August 13, 2013 1:18 AM
Points: 5,956, Visits: 285

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/
Post #100428
Posted Monday, February 16, 2004 8:35 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, November 10, 2014 7:17 AM
Points: 295, Visits: 284

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
Post #100535
Posted Monday, February 16, 2004 10:01 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, September 6, 2005 10:53 AM
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)

 




Post #100550
Posted Monday, February 16, 2004 1:14 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, November 10, 2014 7:17 AM
Points: 295, Visits: 284

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
Post #100588
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse