Moving off a previous thread: DB design issue : storing data 80% alike.. one table possible?

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 have attached my latest DB diagrams, any comments would be helpful. They are both the same picture, one can be zoomed in on to read easier.

The datatypes are not "real" just placeholders, I am more intrested in overall table relationships at this point.

All customers data will contain address information (stored in the customer_jobs) table.

Data unique to a single customer, example is CustomerA, will be split into a table just for that customer/product. A lookup table to allow a join will be the customer_DataTable table. (This has been suggested as "correct" by others but I'm not sure just how to use this with standard "SELECT" and "JOIN"..)

customer_DataTable {customerID, productID, customerTable} where customerTable would be a string value with the name of the table to join to. Now I'm left with the problem that I'm storing table names in tables(which I thought was a bad idea), if its not a bad idea, how should my queries look?




most likely, I'm needing some basic DB help here...

Reflection on research : It looks like having table names -in a table- is wrong. I can get queries to work using dynamic SQL but that still feels wrong.

Wants :

Proper relational database design.

I want a sane way to know which customer data table goes with a specific customer/product mix. (represented in a table)

I don't want address information replicated across 100's of tables(once per customer/product).

I don't want to code 100's of stored procedures to do the "joining" of the address data to the proper customer record.



Any help?