March 3, 2010 at 8:39 am
Today's article by Brad McGehee (March Question: What is the biggest mistake/problem you have ever found on a SQL Server instance, and how did you fix it?) made me think of something that we have here. I hesitate to call it a mistake, because I'm not sure at this point it is. But it is, to my way of thinking, very odd. Let me give a little background and then explain the issue, so you'll see what I'm saying.
I work for a nonprofit agency. I've been here for a long time. It is our business to help clients with substance abuse addictions to find treatment, and to fund the treatment for those without the means to do so themselves. I started at the beginning of this agency, and so I've helped it grow, technically. As a developer, I helped shape the database and write all of the software we use, to do our business. Back when we started there was no one else who did this. Anyway, one of the things that this sort of business does is collect lots of paperwork on clients. We have 14 years worth of paperwork, and it has become very onerous for us, as there's so much of it. Well, our director looked about for a solution and found a company that specializes in helping hospitals and doctors offices archive their medical records, by scanning them and then storing the electronic images of the photos. This company has an application they wrote to track all of the images associated with a patient. This was close enough to our own business model (we have clients, and patients, as we're not a hospital, but as far as records go it is basically the same). So, we purchased a license to their application. One of the attractions of this third party company was the fact that their application uses a SQL Server database for tracking all of the scanned images related to a client. We used our client and case numbers from out database, to associate with the images scanned in. And now we're working on eliminating years worth of paperwork, especially the oldest. (The newest clients paperwork can't be shredded yet, due to regulations, but we can certainly scan in the oldest and then shred it.) So, all of this is working out well for us, and we're making significant reduction in the amount of paperwork we've had on old client charts.
This is our first major third party application we've purchased. Being a developer and a neophyte DBA, I was curious to know the vendor's database schema. I figured I could always learn something by looking at how someone else has solved their problems. So I get into SSMS and started poking around. The first thing I did was open up the vendor's database in SSMS and tried to take a look at the Database Diagrams. There were none, but more significantly there wasn't any way to generate them, either. (Believe me, I tried.) So then next thing I did was start opening individual tables and looking at their structure. This confirmed what I saw in their database diagrams, for although the tables have primary keys, not one table is related to any other table in the database. No foreign key constraints at all. There are 111 tables in all, and I think I've checked each one, and didn't find any relationship specified between any of the tables.
As I said, this is our first major third party application and database, so I don't have a lot of experience at this sort of thing. But I have to ask, is this normal? From a programmer's point of view, I see that what they're doing is making whatever relationships exist, to be enforced and applied through their application. (I've not disassembled it, and I won't, but that must be what's going on.) I can see how not specifying relationships in a database would make it very hard, if not impossible, to develop your own application that works against their database structure. I appreciate that, and frankly I don't want to write any app that utilizes their database. I don't have the time; I've got enough of my own work to keep me busy, thank you. But I would like to know if what this company has done is common practice.
Kindest Regards, Rod Connect with me on LinkedIn.
March 3, 2010 at 9:58 am
definitely not normal. foreign key relationships are a best practice.
I've encountered this before, and it seems the companies think that their schema would reveal their business processes ro something, so they remove the keys on purpose. I think companies that do this are not very experienced and they don;'t know any better yet.
We had a company provide a database like that, and it ended up going straight to the company's lawyers lap, because they had a contract that said they followed standard business practices, and the removal of them was not one of them; they ended up losing the contract and having to refund moneies because of that.
Lowell
March 3, 2010 at 10:25 am
Well, I wouldn't say it was normal either - but I have seen it quite often. Mostly where the company wants to be able to deliver their product on multiple SQL products (e.g. SQL Server, Oracle, etc...).
Instead of putting any DRI in the database, they put it all in their application assuming that the only way data will ever be accessed on that database is through their application.
You'll also notice in these systems that there are very few (if any) stored procedures or views. All code is generated in the application and sent to the backend database.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
March 3, 2010 at 10:40 am
I would say that a lot of 'mature' systems that were written on earl(ier) SQL compliant platforms will not include relationships or constraint parameters.
Referential integrity in the database is not necessary to writing good applications (though it can help - a lot!) and as long as the data validation and integrity is enforced by the front end application it is not normally an issue.
however I would expect to be able to work out the main tables and their relationships with similar named columns if the design is any good (and in your language.. I once had to work with a database with half the tables and fields in Spanish - but you might have spanish table and english field names...)
March 3, 2010 at 1:58 pm
Lowell (3/3/2010)
definitely not normal. foreign key relationships are a best practice.I've encountered this before, and it seems the companies think that their schema would reveal their business processes ro something, so they remove the keys on purpose. I think companies that do this are not very experienced and they don;'t know any better yet.
We had a company provide a database like that, and it ended up going straight to the company's lawyers lap, because they had a contract that said they followed standard business practices, and the removal of them was not one of them; they ended up losing the contract and having to refund moneies because of that.
I don't know if the third party company promised to meet stardard business practices or not. I wasn't a part of that process. We'll see how it goes.
Kindest Regards, Rod Connect with me on LinkedIn.
March 3, 2010 at 2:02 pm
Jeffrey Williams-493691 (3/3/2010)
Well, I wouldn't say it was normal either - but I have seen it quite often. Mostly where the company wants to be able to deliver their product on multiple SQL products (e.g. SQL Server, Oracle, etc...).Instead of putting any DRI in the database, they put it all in their application assuming that the only way data will ever be accessed on that database is through their application.
You'll also notice in these systems that there are very few (if any) stored procedures or views. All code is generated in the application and sent to the backend database.
You're right! I just checked and there are no views at all in this database. There are stored procedures, but just a little more SP's than there are tables. For the most part each SP is a simple INSERT into the similarly named table. There are a few SP's that also do updates, but that's it.
Kindest Regards, Rod Connect with me on LinkedIn.
March 3, 2010 at 2:30 pm
That sounds like good coding to me.
All of the business rules about how data gets validated and entered into the table is held in the SPs.
Any user interface gets and submits it's data via the SPs. This means that the user interface layer is interchangable between thin client (.net) and ASP (web based) and/or mobile apps because the interface doesn't need to told all the business rules. If you need to change the way that data is stored or the validation that is required, you change it once in the business layer and all interfaces respond in the same way.
look for n-tier architechture....
March 3, 2010 at 2:47 pm
aaron.reese (3/3/2010)
That sounds like good coding to me.All of the business rules about how data gets validated and entered into the table is held in the SPs.
Any user interface gets and submits it's data via the SPs. This means that the user interface layer is interchangable between thin client (.net) and ASP (web based) and/or mobile apps because the interface doesn't need to told all the business rules. If you need to change the way that data is stored or the validation that is required, you change it once in the business layer and all interfaces respond in the same way.
look for n-tier architechture....
That doesn't explain the lack of relations. leaving the database in an inconsistent state when one of your "business tier" procs blows up just means you now have junk. All it takes is one junior developer with a bit too much partying the night before and poof, there goes your data relationships.
There are also a lot of cases where T-sql isn't the best tool to express business logic, often leading to sub-par performance in the procedures.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply