DB design issue : storing data 80% alike.. one table possible? use XML?
Here is my issue/question :
I'm looking at redesigning a data processing system we have at work.
In trying to design a new DB, I have most issues worked out to a 3NF system, except for customer data.
The common theme of our customer data is that : every customer wants to us to make a product, then *mail* it out.
Each item to produce must have an address... thats the commonality.
So that means that every record will have the same information except for a few items of customer specific data.
Right now we have about 12 active customers, but plan for a great deal of growth in the coming year(s). I don't like the idea of creating new tables each time we get a new customer. Is this a justified concern?
The methods I have thought of to deal with this:
1) Single "Data" table with a XML column to store customer unique data.
2) Create a table per product to store the unique items for the customer.
2a) in the master data table have a column that stores this customer table name.
2b) have a CustomerProduct -> CustomerDataTable table
As a developer I can work with #1, but not sure about the DB usability going forward.
solution #2 seems more like a correct DB design, but what problems will I encounter using a strategy like that?
If I need to I can provide examples, I know its pretty vague at this point.
"Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
"I have my standards. They may be low, but I have them!" - Bette Middler
"It's a book about a Spanish guy named Manual. You should read it." - Dilbert
Customer2 : 987 1st Street, City2, VA, 54321, eyeColor, Height, Weight, Sex
Customer 3 : 21 Jump Street, New York, NY, 22222, badgeNo, Classification, Rank, DepartmentNo, DeskNo, licenseToKill, status
Each record will be enough to "build" a product specific to that customer. Each customer sends in separate data files built to their own specification.
As you can see, all the data records contain mailing address information, but each customer also sends in number of other pieces of information for a record. Also the number of pieces of data beyond address info a customer sends is customer specific, one might have 3 fields while another might have 12 or 20. (in the above examples Customer3 has many more fields than 1 or 2, and no customer sends in the same data items.)
I've attached my DB layout so far, the datatypes are not set yet but rather just there to show the structure so far.
The table in question is my "customer_jobs" table.. Right now all it stores is address data. I would like to also hold the customer specific items in that table, but thats my issue, I don't know the best way to do so, and I can't really claim that doing so is the proper way to design this DB.