Results 1 to 10 of 10
  1. #1
    Join Date
    Dec 2003
    Location
    New jersey, USA
    Posts
    9

    Basic Data Model

    Hello All,

    Any suggestions, web sites, books, etc. on finding a Data Model for customers and their orders?

    I am having trouble conceptualizing tables to relate 1 customer to multiple addresses, multiple phone numbers, and multiple phone numbers within 1 address. I keep going in circles.

    I think I would understand better by looking at an example.

    Thanks for your help.

    Scolanm

  2. #2
    Join Date
    Dec 2003
    Location
    Canada
    Posts
    710

  3. #3
    Join Date
    Oct 2003
    Posts
    706

    Re: Basic Data Model

    Originally posted by Scolanm
    I am having trouble conceptualizing tables to relate 1 customer to multiple addresses, multiple phone numbers, and multiple phone numbers within 1 address. I keep going in circles.
    Phone numbers and addresses can get you into a lot of trouble. The trouble is how to (1) store all the various possibilities, and yet (2) fetch the customer's current information quickly for mailing labels and so-forth.

    What I usually do is to store the "current" information into fields within the customer record, and store the alternates in a separate table linked by customer-ID. When the fields in the customer-record are changed, a new alternate-entry is added automagically. Buttons are provided to copy the data from any selected alternate into the main fields.

    This may not be "correct," it may not be "Nth normal form" or what have you, but I find that it works. Like so many things it's a compromise, designed to facilitate what people actually need to do with this kind of info.

    Also: I store the address-information that was used for (say) an invoice in the invoice record. It's copied from the customer-record; yessir, I said [u]copied[/i]. Yessir, a duplicate copy of that data now exists! This is simply because in the real world, things change. And also, even if a customer's address changes in the future you still need to know where a particular document was sent, in the past.

    You'll find all kinds of compromises against tradeoffs in application-design. There are no absolutes. The books cited are useful as examples and as reference. Other very good examples are existing products like Quicken and Act! which have gained widespread user acceptance.
    ChimneySweep(R): fast, automatic
    table repair at a click of the
    mouse! http://www.sundialservices.com

  4. #4
    Join Date
    Dec 2003
    Location
    New jersey, USA
    Posts
    9

    Red face

    Thanks for the replies.

    My initial thought is to have a "core" table of customer information, using a unique "customer number" as the PK, in the same way a social security number is a unique for only one person.

    Then I thought to create separate tables for each "catagory" of repeating info that relates to each customer, using the unique "customer number" as the foreign key. A table of "Alternate Addresses" and "Alternate Phone Numbers" and "Customer Orders" , etc. could be associated with a single "customer number".

    This makes sense, but at the same time seems too simplistic to effectively work. Unfortunately I am not sure of the pitfalls and tradeoffs of this type of model.

    The ultimate goal of collecting and storing all this info is to be able to better serve our customers, and perform marketing analysis on purchasing demographics.

    The ease and effectiveness of being able to use the data is most important, rather than textbook normalization.

    Any thoughts?

    Thanks,

    Scolanm

  5. #5
    Join Date
    Oct 2003
    Posts
    1,091
    I am relatively new to database ddesign, but I found the book Database Design for Mere Mortals by Michael Hernandez to be very helpful. He goes through every step of designing, especially the 1 to many issues.

    http://www.amazon.com/exec/obidos/tg...glance&s=books
    Last edited by shades; 12-31-03 at 11:59.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002

    Re: Basic Data Model

    Originally posted by sundialsvcs
    Also: I store the address-information that was used for (say) an invoice in the invoice record. It's copied from the customer-record; yessir, I said [u]copied[/i]. Yessir, a duplicate copy of that data now exists! This is simply because in the real world, things change. And also, even if a customer's address changes in the future you still need to know where a particular document was sent, in the past.
    i would just like to point out that this is absolutely the best design from a practical point of view

    the issue brought up by this example is the "point-in-time" nature of the data, and while there are other ways of designing address data to take changes over time into consideration, storing the address into the invoice is not only correct, but simple, and therefore elegant

    i cannot tell you how many database designs i've seen that were screwed up by some data modeller's insistence on generalizing everything

    never lose sight of the goal: a flexible, maintainable design that will be easy to change in future as additional requirements are added to the system, but which is at the same time simple and practical so that you can implement the system today within budget


    rudy
    http://r937.com/

  7. #7
    Join Date
    Dec 2003
    Location
    Canada
    Posts
    710
    i cannot tell you how many database designs i've seen that were screwed up by some data modeller's insistence on generalizing everything.
    I have to agree. Often the rules of the business are sacrificed for some epiphany by the modeler. It is important to keep a tight reign on the design team to make sure that what has to be done in the interests of the business are done.

    However, I also want to stress there is always more than one solution, even more than one "elegant" solution.
    Last edited by certus; 12-31-03 at 12:19.

  8. #8
    Join Date
    Dec 2003
    Location
    New jersey, USA
    Posts
    9
    Thanks. This is exactly the kind of imput I need.

    Fortunately the goal of my development efforts is to make the system as effective as possible with respect to the goals of the business process. Standardized conventions are effective in so far as they contribute to the success of the business process (which is why we are here in the first place).

    Any other ideas, suggestions, cavaets, tips, gotchas, be-aware-ofs, don't-overlooks, considerations, etc. to real world effectiveness would be greatly appreciated, as my only resource thus far is "textbook" information, which is a good guide, but experience has for me always been the best guide.

  9. #9
    Join Date
    Jul 2003
    Posts
    74

    Re: Basic Data Model

    [QUOTE][SIZE=1]Originally posted by Scolanm
    Hello All,

    >Any suggestions, web sites, books, etc. on finding a Data Model for >customers and their orders?

    >I think I would understand better by looking at an example.
    In that case, have a look at this Kick-Start Data Model for Customer Addresses :-
    http://www.databaseanswers.com/data_...mer_addresses/

    I think it's important to define and design the logical solution first,(in this case the Normalized Data Model), and then consider how it should be denormalized for performance and ease-of-use by Developers.

    There are other Models on this Site that include more details on various aspects of Customer Data, including Orders.

    B.Dimple
    Junior DBA

  10. #10
    Join Date
    Dec 2003
    Location
    New jersey, USA
    Posts
    9
    GREAT, GREAT SITE. THANKS.

Posting Permissions

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