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

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.