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.

Thanks for any help,
JF

Sounds like you need two (or more) tables:

1. Customer Table:
Customer ID, Full Name, Street Address, Suite/Apt/Box#, City, Province/State, Postal/Zip Code, Country, Phone Number, Fax Number, E-mail Address, Additional Information

2. Shipping Table:
Package ID, Customer ID, Quantity Ordered, Date Ordered, Price per Unit, Quantity Shipped, Date Shipped, Shipping Charges, Date Invoiced, Date Paid, Amount Paid, Amount Due.

You may also need a Product Table, if you have more than one type of product that you ship. Which would mean adding a Product ID to the Shipping Table.

Here is what I have so far (and its not complete), in a basic outline :

customers
customer_contacts
contact_types (primary, secondary, technical, sales, etc...)

products ( customer specific product ID and info )

inventory (info and status of supplies)

jobs (to be used for record keeping and queuing of work)
jobs_billing (jobs might have multiple invoices or billing line items)
jobs_mailing (jobs might have multiple mailings)



My issue lies in the main data table :

Say customerA has data items : CA1, CA2, CA3

and customerB has data items : CB1, CB2

It could easily be

CustomerData
recordID, customerID, address1, address2, city, state, zip, etc.., XML

where XML could store either customerA or B's data.

or

CustomerData
recordID, customerID, address1, address2, city, state, zip, etc.., ProductID, customerUniqueTableName

then

customerA_Data table :
recordID(FK to CustomerData), CA1, CA2, CA3

customerB_Data table :
recordID, CB1, CB2



I guess the more appropriate solution using seperate data records would be to have a table :

Customer_Products_Table table :

CustomerID, ProductID, CustomerDataTable_name





Does this add anything to the conversation?

Arte you still considering a separate table for each customer???

(implied by)
Customer_Products_Table table :
CustomerID, ProductID, CustomerDataTable_name

I'll sum up the issue:

I need to store "customer data records", consider the number of customers to be very high... say 100's.

A common element to all customer data is address information.

Every customer has unique items though, that will not be common to other customers.


Simply put, I would like the best model for this "customer data" issue. I know I'll need many, many more tables in the DB but this is the only issue I'm fighting with.


I see many solutions:

1 table per customer, entirely unique to their setup. <--- Ugly, there has to be a better solution

1 table : using column names like CustomerData1, CustomerData2, CustomerData3, etc... <----- column names need to mean something specific, bad solution

1 table : using an XML column <---- Never used it before... one reason I'm very hesitant to implement.

1 main table + 1 table per customer : the main table would store address information, while the other tables would contain the "unique" columns. <--- I can also see this being viable.



I'm really just asking for flat out help on this.. I'm working on setting up the rest of the schema so I can display here, but thats going to take a bit of work.

When you say each customer has "unique items not common to other customers", are you referring to products only they buy?

If so, I would put them in your product table with a CUSTOMER ID link for the customer(s) that purchase them.

My company produces items for customers : Letters, Gift Cards, Membership Kits, etc..

Each "product" gets mailed out, but each product has unique data on it.


Here is an example of afew data lines I might receive (comma delimited):


Customer1 : 123 Main St., SomeCity, TX, 12345-1234, MemberID, Message, MemberPin


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.