Attention: We are retiring the ASP.NET Community Blogs. Learn more >

How do you make your datastore as versatile as your code?

I'm not an expert on design patterns at all, even though I use them even when I don't realize it. It just happens in OOP. (This is a good time to mention that the Head First book is surprisingly awesome for a topic that's otherwise a snoozefest. The examples are Java, but easy enough to get if you know C#.) I certainly have no problem coding stuff that applies different business rules and logic depending on what you throw at it.

Where I struggle a little is the persistence of data. For example, I'm on a project now where it's clear that different data will be required in certain cases for certain customers, but I can't anticipate what those new data items will be. Modifying the structure of the database constantly is not a very good option.

How do you deal with that? If I suddenly have a new group of customers that need to track a user's hair color, what do I do in terms of my database to accommodate that?

5 Comments

  • I don't understand what's wrong with making enhancements to your database. If you separate your Data Access layer from your business objects, and unit test your code, you should be able to make whatever changes you wish.



    I usually have the DataAccess operations as external objects to the Data Object they're acting on. For instance, if I have a class called "Employee", I might have an interface called IEmployeeAccess that has a Load(Employee) and Save(Employee) methods. Then I would have an implementation of IEmployeeAccess for Sql Server 2k, Sql Server 2k5, XML, Flat Text, or whatever else I wanted to be able to store an employee object in.



    Is this what you're asking? or am I just misunderstanding you?

    Chris

  • With the project I'm on, there would be a ridiculous number of changes being made constantly to the point where, who knows, there could be a hundred columns before too long.



    I won't give the specifics of the project, but let's compare it to an insurance calculator that collects different data by state. Some states collect certain things, others by law can't collect certain things. To make it more fun, these requirements change often more than once in a year. Creating a one-size-fits-all bucket for this data is not possible.

  • Intriguing.

    In the example you gave, here is something I thought of:



    You could separate related information into multiple child tables even if you are forced to use a lot of one-to-one relationships. You could define a corresponding set of rules for the data (show this, don't show that, allow change this, don't allow change that) as a separate set of tables.



    If enhancements were always added as a) the enhancement itself, and b) the rule-state, then you would have a configurable system that could be a one-size-fits-all solution. Of course, you'll have to be the best judge of whether this could work or not.



    Something I've done is provided a "custom" table for our clients--as each client has some special piece of data, they can basically define whatever custom data they wish. I've created separate custom data tables for each primary entity in the system, so they can have the benfit of forign-key constraints and whatnot.



    Sounds like you've got your hands full though--Good Luck!



  • So what you're saying is that it makes sense to store data common to all customers in "normal" tables and have a pair of tables that define and store custom data? That's kind of what I was thinking. I think that would likely perform pretty well. The big question I suppose is how one would type the custom data. I hate to say I don't see an alternative other than strings, but I don't see one. :)

  • You could ask the users what type the data was and build a set of classes to generate the appropriate ALTER TABLE statements. Alternatively, you may be able to use the SQLDMO.dll to some of this work.

Comments have been disabled for this content.