Results 1 to 8 of 8
  1. #1
    Join Date
    Jan 2008
    Posts
    11

    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.

    Thanks for any help,
    JF
    Last edited by JohnFlyTn; 01-28-08 at 13:20.

  2. #2
    Join Date
    Aug 2003
    Location
    Toronto, Ontario, Canada
    Posts
    203
    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.
    When it rains, it pours.

  3. #3
    Join Date
    Jan 2008
    Posts
    11
    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?

  4. #4
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    Arte you still considering a separate table for each customer???

    (implied by)
    Customer_Products_Table table :
    CustomerID, ProductID, CustomerDataTable_name
    Lou
    使大吃一惊
    "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


  5. #5
    Join Date
    Jan 2008
    Posts
    11
    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.
    Last edited by JohnFlyTn; 01-28-08 at 17:42.

  6. #6
    Join Date
    Aug 2003
    Location
    Toronto, Ontario, Canada
    Posts
    203
    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.
    When it rains, it pours.

  7. #7
    Join Date
    Jan 2008
    Posts
    11
    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.)

  8. #8
    Join Date
    Jan 2008
    Posts
    11
    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.
    Attached Thumbnails Attached Thumbnails db_layout.bmp  

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •