Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Havremunken
Starting Member
2 Posts |
 Posted - 2007-06-04 : 04:15:50
|
| 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 keycampaign - foreign key to campaignsettings campaignid fieldfield - varchar, the name of the column in the campaign tabledesc - a description of what goes in the field (used in the sales registration app)default - a reasonable default value showing the user what to registerUsing 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 |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2007-06-04 : 08:52:10
|
| Question 1: It's metadata driven and is quite common for CRM systems. It's not actually defining the tables just the labels that are used by the front end on the fields. As long as you restrict the type of processing that can be done on the custom fields then it shouldn't be a problem.Question 2: You could create views to enable an application treat the custom fields as table data - depends what the app needs to do.Question 3: It's not an unreasonable method as it means that campaigns can be configurable without recourse to development.Another option is to create separate tables for the custom fields for each campaign but that means changing the database structure for each campaign. The other option is to have a lot of custom fields only a few of which are used for any campaign - but you would need some way to record which fields are used so you would be back to something similar to what you have now.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
Havremunken
Starting Member
2 Posts |
Posted - 2007-06-05 : 02:18:28
|
| Ok, I see - I think we'll stick with the basic structure then, and perhaps dynamically create some views when a new campaign is created. Thank you very much for your input. :) |
|
|
|
|
|
|
|
|
What do you call this type of design? Is it evil?