Ways to circumvent a bad database schema?

Question

Our team has been asked to write a Web interface to an existing SQL Server backend that has its roots in Access.

One of the requirements/constraints is that we must limit changes to the SQL backend. We can create views and stored procedures but we have been asked to leave the tables/columns as-is.

The SQL backend is less than ideal. Most of the relationships are implicit due to a lack of foreign keys. Some tables lack primary keys. Table and column names are inconsistent and include characters like spaces, slashes, and pound signs.

Other than getting a new job or asking them to reconsider this requirement, can anyone provide any good patterns for addressing this deficiency?

NOTE: We will be using SQL Server 2005 and ASP.NET with the .NET Framework 3.5.

Answer 1

Easy: make sure that you have robust Data Access and Business Logic layers. You must avoid the temptation to program directly to the database from your ASPX codebehinds!

Even with a robust database schema, I now make it a practice never to work with SQL in the codebehinds - a practice that developed only after learning the hard way that it has its drawbacks.

Here are a few tips to help the process along:

First, investigate the ObjectDataSource class. It will allow you to build a robust BLL that can still feed controls like the GridView without using direct SQL. They look like this:

<asp:ObjectDataSource ID="ObjectDataSource1" runat="server" 
    OldValuesParameterFormatString="original_{0}" 
    SelectMethod="GetArticles"   <-- The name of the method in your BLL class 
    OnObjectCreating="OnObjectCreating"   <-- Needed to provide an instance whose constructor takes arguments (see below)
    TypeName="MotivationBusinessModel.ContentPagesLogic">  <-- The BLL Class
    <SelectParameters>
        <asp:SessionParameter DefaultValue="News" Name="category" <-- Pass parameters to the method
            SessionField="CurPageCategory" Type="String" />
    </SelectParameters>
</asp:ObjectDataSource>

If constructing an instance of your BLL class requires that you pass arguments, you'll need the OnObjectCreating link. In your codebehind, implement this as so:

    public void OnObjectCreating(object sender, ObjectDataSourceEventArgs e)
    {
        e.ObjectInstance = new ContentPagesLogic(sessionObj);
    }

Next, implementing the BLL requires a few more things that I'll save you the trouble of Googling. Here's an implementation that matches the calls above.

namespace MotivationBusinessModel   <-- My business model namespace
{
    public class ContentPagesItem  <-- The class that "stands in" for a table/query - a List of these is returned after I pull the corresponding records from the db
    {
        public int UID { get; set; }
        public string Title { get; set; }  <-- My DAL requires properties but they're a good idea anyway
        ....etc...
    }

    [DataObject]  <-- Needed to makes this class pop up when you are looking up a data source from a GridView, etc.
    public class ContentPagesLogic : BusinessLogic
    {
        public ContentPagesLogic(SessionClass inSessionObj) : base(inSessionObj)
        {
        }

        [DataObjectMethodAttribute(DataObjectMethodType.Select, true)]  <-- Needed to make this *function* pop up as a data source
        public List<ContentPagesItem> GetArticles(string category)  <-- Note the use of a generic list - which is iEnumerable
        {
            using (BSDIQuery qry = new BSDIQuery())  <-- My DAL - just for perspective
            {
                return
                    qry.Command("Select UID, Title, Content From ContentLinks ")
                        .Where("Category", category)
                        .OrderBy("Title")
                        .ReturnList<ContentPagesItem>();
                 // ^-- This is a simple table query but it could be any type of join, View or sproc call. 
            }
        }
     }
 }

---

Second, it is easy to add DAL/BLL dlls to your project as additional projects and then add a reference to the main web project. Doing this not only gives your DAL and BLL their own identities but it makes Unit testing a snap.

Third, I almost hate to admit it but this might be one place where Microsoft's Entity Framework comes in handy. I generally dislike the Linq to Entities but it does permit the kind of code-side specification of data relationships you are lacking in your database.

Finally, I can see why changes to your db structure (e.g. moving fields around) would be a problem but adding new constraints (especially indices) shouldn't be. Are they afraid that a foreign key will end up causing errors in other software? If so...isn't this sort of a good thing; you have to have some pain to know where the disease lies, no?

At the very least, you should be pushing for the ability to add indexes as needed for performance reasons. Also, I agree with others that Views can go a long way toward making the structure more sensible. However, this really isn't enough in the long run. So...go ahead and build Views (Stored Procedures too) but you should still avoid coding directly to the database. Otherwise, you are still anchoring your implementation to the database schema and escaping it in the future will be harder than if you isolate db interactions to a DAL.

Answer 2

I do a lot of data integration with poorly-designed databases, and the method that I've embraced is to use a cross-referenced database that sits alongside the original source db; this allows me to store views, stored procedures, and maintenance code in an associated database without touching the original schema. The one exception I make to touching the original schema is that I will implement indexes if needed; however, I try to avoid that if at all possible.

The beauty of this method is you can isolate new code from old code, and you can create views that address poor entity definitions, naming errors, etc without breaking other code.

Stu

Answer 3

It really depends how much automated test coverage you have. If you have little or none, you can't make any changes without breaking stuff (If it wasn't already broken to start with).

I would recommend that you sensibly refactor things as you make other changes, but don't do a large-scale refactoring on the DB, it will introduce bugs and/or create too much testing work.

Some things like missing primary keys are really difficult to work-around, and should be rectified sooner rather than later. Inconsistent table names can basically be tolerated forever.

Answer 4

I've been here before. Management thinks it will be faster to use the old schema as it is and move forward. I'd try to convince them that a new schema would cause this and all future development to be faster and more robust.

If you are still shot down and can not do a complete redesign, approach them with a compromise, where you can rename columns and tables, add PKs, FKs, and indexes. This shouldn't take much time and will help a lot.

short of that you'll have to grind it out. I'd encapsulate everything in stored procedures where you can add all sorts of checks to enforce data integrity. I'd still sneak in all the PK, FK, indexes and column renames as possible.

Answer 5

I would do an "end-around" if faced with this problem. Tell your bosses that the best way to handle this is to create a "reporting" database, which is essentially a dynamic copy of the original. You would create scripts or a separate data-pump application to update your reporting database with changes to the original, and propagate changes made to your database back to the original, and then write your web interface to communicate only with your "reporting" database.

Once you have this setup in place, you'd be free to rationalize your copy of the database and bring it back to the realm of sanity by adding primary keys, indexes, normalizing it etc. Even in the short-term, this is a better alternative than trying to write your web interface to communicate with a poorly-designed database, and in the long-term your version of the database would probably end up replacing the original.

Answer 6

I would start with indexed views on each of the tables defining a primary key. And perhaps more indexed views in places where you want indexes on the tables. That will start to address some of your performance issues and all of your naming issues.

I would treat these views as if they were raw tables. This is what your stored procedures or data access layer should be manipulating, not the base tables.

In terms of enforcing integrity rules, I prefer to put that in a data access layer in the code with only minimal integrity checks at the database layer. If you prefer the integrity checks in the database, then you should use sprocs extensively to enforce them.

And don't underestimate the possibility of finding a new job ;-)

Answer 7

Since you stated that you need to "limit" the changes to the database schema, it seems that you can possibly add a Primary Key field to those tables that do not have them. Assuming existing applications aren't performing any "SELECT *..." statements, this shouldn't break existing code. After that's been done, create views of the tables that are in a uniform approach, and set up triggers on the view for INSERT, UPDATE, and DELETE statements. It will have a minor performance hit, but it will allow for a uniform interface to the database. And then any new tables that are added should conform to the new standard.

Very hacky way of handling it, but it's an approach I've taken in the past with a limited access to modify the existing schema.

Answer 8

One thing you could do is create lots of views, so that the table and column names can be more reasonable, getting rid of the troublesome characters. I tend to prefer not having spaces in my table/column names so that I don't have to use square brackets everywhere.

Unfortunately you will have problems due to the lack of foreign keys, but you may want to create indexes on the column, such as a name, that must be unique, to help out.

At least on the bright side you shouldn't really have many joins to do, so your SQL should be simple.

Answer 9

Views and Synonyms can get you so far, but fixing the underlying structure is clearly going to require more work.

I would certainly try again to convince the stakeholder's that by not fixing the underlying problems they are accruing technical debt and this will create a slower velocity of code moving forward, eventually to a point where the debt can be crippling. Whilst you can work around it, the debt will be there.

Even with a data access layer, the underlying problems on the database such as the keys / indexes you mention will cause problems if you attempt to scale.

Answer 10

If you use LinqToSql, you can manually create relationships in the DBML. Additionally, you can rename objects to your preferred standard (instead of the slashes, etc).

Answer 11

can anyone provide any good patterns for addressing this deficiency

Seems that you have been denied the only possibility to "address this deficiency".

I suggest adding the logic of integrity check into your coming stored procedures. Foreign keys, unique keys, it all can be enforced manually. Not a nice work but doable.