SQLServerCentral / Discuss Content Posted by Frank Kalis / Article Discussions / Article Discussions by Author / An Introduction to Database Models / Latest Posts

RE: An Introduction to Database Models

Frank Kalis — Sat, 28 Feb 2004 14:54:00 GMT

Hi Thomas,surely a little bit late for a reply, but here it goes.Yes, the human brain is the most powerful database around. Curious how the indexes would look like. Now as I have read the responses and thought over them for a while I'm glad that I cancelled my idea of writing an article about moving object databases. I think it would only lead to confusion here ...and after all it is not SQL Server related anyway.

RE: An Introduction to Database Models

ThomasH — Tue, 17 Feb 2004 12:11:00 GMT

Frank, nice to see you back. I've also been gone a couple of weeks (not voluntarily - on the road/busy).

Thank you Frank and to all of you for making interesting forum article reading.

I hope I don't ramble to much here, but I do have some thoughts...

I'm by no means a theoretical database expert, or for that matter cared very much aboutthe theoretical reasons for databases and how the data model is designed. In my opinion,the database and DBMS exist for the purpose of recalling information in a form and accuracy required by the querier. Personnally I've tended to gravitate toward DBMSes thatprovide me with a rich programming environment "close" to the data, while providingthe performance required by the application(s). We all know that given enough time, wecould write interfaces to make most all the DB models "look" the same to the end user,and also the programmer. At this time the relational model, the DBMSes that supportit, seem to provide the best (in most) of the database & programming aspects we all consider important.

I think we mostly like the relational model because it's the closest thing to the typeof data we deal with most of the time: "Business Data". Since this is the kind of datawhere the "money" is, it's had the most effort put towards it. This is why we have everevolving and better DBMSes as the computer age matures. I think the way todays computersare built, and the way business conducts itself, has created the databases & DBMS we seetoday.

Here comes the Sci-Fi part...

I agree with Frank on the notion that the future holds exciting new database models to discover. I've always wondered about the merger of motion pictures (and now with DVD, random access motion pictures), virtual reality (VR) and databases. Remember the "goal" ofthe database: "To recall information". I can imagine a VR interface to database data, suchas Lawnmower Man movie kind of portrayed, where the user navigates to the targeted informationusing the basic "human with world" interface(s). Whether it's voice recognition or just plainold thought that formulates the "query", the end result is that the user recieves the information requested.Another slant I have, is the human brain itself. What a wonderful database! All kinds of inputsources, recall methods, fuzzy logic, and even "imagination". Imagination = make new data fromsometimes nothing, WOW! I know your saying, or at least I'm thinking... what about data integrityand accuracy? The brain fails so often in this respect. Well my answer to that is: some data doesNOT have to be completely accurate. Let me give some examples. Any of us that have tables that contain street addresses, most of the time do not care if the user typed in "123 Main Street" or"123 Main Str.". Also, in some high volume DBMS COUNT() may produce inaccurate results. So do wecare about accuracy? Sometimes. The brain seems to figure this out for us pretty good. i.e. Itrecalls accurately my wife's birthday (most of the time). Also seems to keep my heart beatingwhile I sleep, and has done this unfailingly, or at least to my satisfaction for many years. It can also recall some thing with great accuracy such as anything times zero equals zero. We also"know" things like a red traffic light means stop etc. These are "learned". Other things our brainjust knows on it's own. So I think data integrity and accuracy are not absolutes, more degrees of suitability to the task at hand. There are many comparisons to be made between how our brain worksand how users want to use computer databases. I also think that accuracy and reliability can be build into man-made databases that can overcome "flaws" that natural databases may have in them.There is another "database" that is extreamly facinating... The DNA molecule and it's chromosomes.Two "databses", combining to make a new, unique structure, and likely containing more data thanany of us ever deal with. I know this is biological stuff, but the biological world is the only real place to look, outsideof our own human inventions, for things that are "self changing" or "alive".

I think the relational model will be used for business databases for quite sometime. Other applications such as medical/scientific and entertainment will most likely use proprietarydatabase models. Only if they achieve "critical mass" (enough $$$$) would any of them becomemainstream. Relational databases will be used (and stretched) for tasks having little to do with business data, because it is mainstream, and more resources are available to accomodate the needs quickly. This my be close-minded on my part, but I think until computers operatein a very different manner than they do today, the database models available to us will bepretty much a form of what we using today, just much more advanced perhaps as time goes by.

One thing the relational data model does not do well, or not at all, is "analog" pattern matching.Example Query: Name the people who are in the same photograph with me within the last year, who are not relatives....etc. You get the picture (pun intended). Future database models mighthandle stuff like this, and might even handle business data better than the relational modeldoes. Who knows? I think this type of database ability goes beyond "Type" support referenced by Chris, and requires other/different database models.

Just my $.02

RE: An Introduction to Database Models

Tatsu — Mon, 16 Feb 2004 13:14:00 GMT

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.

RE: An Introduction to Database Models

thormj — Mon, 16 Feb 2004 10:01:00 GMT

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)

RE: An Introduction to Database Models

Tatsu — Mon, 16 Feb 2004 08:35:00 GMT

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.

RE: An Introduction to Database Models

Frank Kalis — Sun, 15 Feb 2004 15:12:00 GMT

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?

RE: An Introduction to Database Models

Steve Jones - SSC Editor — Fri, 13 Feb 2004 19:33:00 GMT

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!

RE: An Introduction to Database Models

cs_troyk — Thu, 12 Feb 2004 15:09:00 GMT

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

RE: An Introduction to Database Models

Adam Machanic — Thu, 12 Feb 2004 11:03:00 GMT

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.

RE: An Introduction to Database Models

thormj — Thu, 12 Feb 2004 10:58:00 GMT

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:

Where will the object run (Service, Webserver/ice)?
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

RE: An Introduction to Database Models

Adam Machanic — Thu, 12 Feb 2004 10:07:00 GMT

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?

RE: An Introduction to Database Models

Tatsu — Thu, 12 Feb 2004 09:42:00 GMT

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.

RE: An Introduction to Database Models

Adam Machanic — Thu, 12 Feb 2004 09:27:00 GMT

Tatsu:

Are you talking about using a queing system to serialize database access?

RE: An Introduction to Database Models

Adam Machanic — Thu, 12 Feb 2004 09:24:00 GMT

Thor:

There is a fine line between "business rules" (e.g. constraints) and "business logic" (e.g. if user enters something in this field, pop up this message)... It really needs to be determined on a case-by-case and application-by-application basis. Some apps are, by nature, much more data-driven than others. However, on the whole I've seen much less dependency on the database for constraints than I think there should be. One of the primary points of the database is the enforce data integrity, yet most shops prefer to do that all in the application layer and use the database only as a data store. That, IMO, is a mistake.

As for a way to pass an event from the db to an application, you can look into writing extended stored procedures. Yet another part of the database that's under-used, IMO!

RE: An Introduction to Database Models

Tatsu — Thu, 12 Feb 2004 09:20:00 GMT

Triggers vs. Polling

This is a trade-off decision.

Is the trigger load greater than the load would be if you polled every few minutes? If the database is getting hammered then the trigger load is likely higher than would be expected for polling at intervals.

How "real-time" do the alerts need to be? If you can wait five minutes or more then polling looks a little more attractive.

What is the project schedule like? Seriously though, if you can take time out for quality, you can theoretically eliminate both. I haven't doen this yet but I have been thinking about it. If your application is using .NET or a similarly capable platform (Java should be able to handle it along with many other OO platforms) you could write a data access layer using a single-call type methodology where there is only one set of the classes instantiated at a time and all calls to update the DB are handled through this set of classes. That way you can perform the update and send a notification via event and/or delegate to any other classes that are tied to it. It sounds pretty complex but once you worked out the details I bet you could come up with a code generator that would build the classes for you (See my earlier post).

RE: An Introduction to Database Models

thormj — Thu, 12 Feb 2004 09:06:00 GMT

There isn't anything the relational model can not handle for OO data storage. The only thing it can not handle is the behavior/methods/operations/pick-your-term. And by all rights it has no business performing actions on the data since that is clearly business logic.

I'm very new to database-isms, but I was making my database a very active one (ie, code the business rules into the database as triggers, stored procedures, and tasks; for example, when an alarm gets inserted into the db, a trigger fires off and if the alarm matches a mask in a different table, the database would email me using sqlmail).

Your comment makes me wonder if that is really the way I should do it, so here are my newbie questions:

Why separate the business logic into another compartment? Where/how would it run?
Is there a way to pass an event from the db to an application (db says "New Data in table Foo.", instead of the app pulling a dataset every few minutes and detecting changes -- is there a way to do this w/o pulling a whole dataset)?

We're planning on having some events that run every minute, others that run daily, and some others (like the alarms above) that run based on row(s) modified in the table.

Any reason my db should be quiet place of solitude vs a "Active Do-All Buzzing Database"?

TIA,Thor

RE: An Introduction to Database Models

Adam Machanic — Thu, 12 Feb 2004 07:51:00 GMT

An article by C.J. Date on data models, what is a data model, and (more importantly?) what's not:

http://www.dbdebunk.com/page/page/622923.htm

RE: An Introduction to Database Models

Tatsu — Thu, 12 Feb 2004 06:59:00 GMT

I would like to add a little bit of personal opionion on the OODB topic.

The concept of an Object-Oriented Database just seems wacky to me. Objects imply action. If you're thinking in terms of Object-Oriented programming, the really important stuff is the behavior of the objects. The data storage is irrelevant. In reality, a relational DBMS is the perfect data store for an OO system. There isn't anything the relational model can not handle for OO data storage. The only thing it can not handle is the behavior/methods/operations/pick-your-term. And by all rights it has no business performing actions on the data since that is clearly business logic.

I worked with another DBA that did some considerable research on OODB's and his conclusion (Which I expected it to be in the first place) was that they are immature constructs for developers that are too lazy to build a code generation engine to develop the code between the data and the business logic. (Quick plig for my favorite code generator - CodeSmith)

RE: An Introduction to Database Models

Chris Hedgate — Thu, 12 Feb 2004 06:26:00 GMT

As I explained in my other posting (in response to the article itself), any lack of performance is not a fault of the relational model but of the implementation of it (i.e. SQL). However, if we do compare SQL DBMSs to hierarchical DBMSs and do find that the latter generally has higher performance (I have no idea if this is true, and I highly doubt it), remember that this performance would come at a loss of integrity. And without the integrity of the database protected, what good is high performance? Also, once again the hierarchical model had several huge problems incl. the lack of separation between the logical and physical design. This leads to problems like the ones I described above regarding OO databases plus the fact that the user needs to know the physical access paths for the data, and is of course one of the reasons why the relational model replaced it 30 years ago. The same goes for XML which as you probably suspect is hierarchical, yes.

RE: An Introduction to Database Models

Chris Hedgate — Thu, 12 Feb 2004 06:16:00 GMT

I don't want to make this into any kind of flame war or anything, but I must address a couple of points here. I do not know what Frank Kalis thinks about the models described as the article is mainly a description of them, so these comments are not made to say he is wrong, they are written to address some points in the article that I think can be wrongly understood or accepted by some readers.

As said in the article, the relational model clearly distinguishes the logical desgin from the physical. The relational model is all about the logical and nothing about the physical part. Therefore it is impossible to say that any other database management model has better or worse performance, because performance is based on the physical design solely. This is in fact the greatest advantage of the relational model, apart from it's theoretical foundation (which no other model has, or at least not as clear), that the decisions you make on logical design issues will not affect the physical design. It is up to the RDBMS to decide how the logical design you create is best handled physically.

For the same reasons, it is also not correct to say that "[the] most significant [of] several weaknesses of the relational model ... is the inability to handle BLOB's." In the relational model, a relation has one or more attributes that are defined as being of specific domains. There is nothing that says that a domain can not be a BLOB or any other type of data. For a complete discussion and description of this see for instance the paper 'What first normal form really means' by C.J Date. Any flaw in handling these domains is purely a fault of the DBMS, not the relational model. Here is a nice quote from the above mentioned paper:

A proper object/relational system is just a relational system with proper type support ... --which simply means it's a proper relational system, no more and no less.

Regarding the OO 'model':

"The developer of such a system is responsible for implementing methods and properties to handle the data in the database from within his object-oriented application. There is no longer a strict distinction between application and database."

I would say that there is no longer a database, or at least not a DBMS. The reason to have a DBMS is precisely to have a centralized system for controlling the database, especially the integrity of it, so that the application developers do not need to (or must not even) know and control how to access the data. Using this approach we regress back to 40 years ago and leave all the work to each application, and we can only hope that each application takes care of what needs to be taken care of. Also, this means that each database you create is heavily linked to a specific application, if you want some other application to access the same data in just a slightly different way you probably need to either make changes to the first application or make the integrity constraints even more complex than what is already necessary using this 'model'. You'll notice that I put the word model in quotes when discussing the OO 'model'. This is because I do not think it is a complete data model, unlike the relational model. See for instance 'Something to call one's own'.

Finally, I have to disagree with the conclusion that "there (still) is a world outside the relational model, which can be pretty exciting to discover". All of these other so-called models are either old ones that were replaced by the relational model because of the flaws they had, or regressions to these old models by re-inventing them and calling them something different. The relational model is based on set theory and predicate order logic, anything trying to replace the relational model must also be a replacement for these theories several centuries old and proven.

With all this said, I still find this a good and interesting article as it brings up the discussion of points like these, and I kind of think that is one of the reasons why Frank wrote it.

RE: An Introduction to Database Models

David.Poole — Thu, 12 Feb 2004 01:58:00 GMT

Interesting!

I thought that hierachal databases were making a come back for very large databases because of their performance advantages.

Are XML databases hierachal?

Do you know if there are there any OO databases on the market at present?

An Introduction to Database Models

Frank Kalis — Sat, 07 Feb 2004 05:46:00 GMT

Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/fk