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

http://www.amazon.com/exec/obidos/AS...873613-6434245

http://www.amazon.com/exec/obidos/tg...glance&s=books

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.

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

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

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/

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.

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.

[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

GREAT, GREAT SITE. THANKS.