What Do You Call This Type Of Design? Is It Evil?

Hi there,

I am new here, and I've tried to see if this topic has been discussed elsewhere, but since I am not familiar with the terminology, I'll stick my neck out and ask.

Quick background: I am a C# developer with limited exposure to and experience with SQL. I now have to make some design decisions for a newer version of a "legacy" app. Since those of us who are left here basically know how to query the database and do the CRUD work, but don't have a lot of mad skills in the db design department, I am wondering to which degree we should leave things alone.

Ok, so now to the actual issue.

This database is used by an application that registers sales locally for certain types of sales teams. All data is eventually extracted and sent off as ASCII files to central processing, but until then, they are stored in a table for each sales campaign.

Information about campaigns is stored in one table called CampaignSettings. Highlights of this table include a CampaignID (primary key) and some generic sales information, plus a reference to the table that holds customer information. Yep, each campaign has it's own table, as customer registrations (not just for sales, but for information requests etc.) can easily scale into the hundereds of thousands, plus the number of campaigns will eventually reach at least 20-50 active at any given time.

Yep, perhaps it sounds like a huge task for inexperienced db people, that's why we have to learn, and fast! ;)

Ok, so these individual campaign tables is where it gets interesting, and they are the reason I am writing. Each campaign table has the same basic structure - a RecordID, an insertion date, several customer information fields. And then they have two additional sets of fields; The salesinfo "nodes", and the "custom fields".

There is a table called sinode, which pretty much looks like this:

id - int, primary key
campaign - foreign key to campaignsettings campaignid field
field - varchar, the name of the column in the campaign table
desc - a description of what goes in the field (used in the sales registration app)
default - a reasonable default value showing the user what to register

Using this method, the idea is that each campaign can have a different structure (i.e. different product types sold = different information needs to be registered).

Also, for the "custom fields" stuff, something similar is employed. There is a scriptvariables table, similar to the sinode one, which contains custom fields that campaigns contain - when they feel like it, they can for instance find everyone with a certain value in a custom field and send them some sales propaganda by snailmail, with some of these script variables used in the letter. The difference between salesinfo columns and custom field columns is basically that only salesinfo columns (plus a few of the standard ones present in all tables) are reported to central processing.

QUESTION #1: Is there a name for such a table structure, where the very definition of tables themselves are somewhat dynamic and applications need to query support tables in order to know the customer table structure for each campaign. Is the name "Brain Dead Design"? ;) Or is this an acceptable way of doing things?

Since we're now upgrading things a little, among other things we're considering moving from "old fashioned" ADO.NET to LINQ or some kind of O/R mapping application, so that we'll cut down on the maintenance of the database <-> object layer.

QUESTION #2: Will an O/R Mapper or LINQ (when Orcas goes RTM) be able to handle the kind of tables we're talking about here, considering that the structure of the individual customer table is not known at compile time, but depends on the database?

And then, there's the question that has been haunting me for a while now..

QUESTION #3: If you, the reader, who with great probability knows a lot more about database design than I do, were put in charge of such a project - how would YOU design it? Keep it like today? Create a huge normalization table for sales items and custom fields? What kind of architecture would be "correct" in this case, considering that our customers scale from the very small with few campaigns to the relative large with big, nasty customer tables.

I will be extremely grateful for any replies, and I promise to read many heavy SQL books as penance, and spend 10% of my work day helping newbies for several weeks once I attain enlightenment! :)

Thanks in advance,

Havremunken