Relationship structure

T

Tim Johnson

I am designing a database in which customers may or may not be a part of a
company, there may be company orders, but the person that "owns" the orders
may vary, as will the person that receives the bill (to top it all off, they
may not even be the same person).

For customers that do not belong to a company, the solution seems to me to
be a clear and simple one-to-many between customers and orders. Companies to
Orders seems like it should be one to many, as well.

However; the relationships between customers, companies, and orders seems a
bit trickier. I have considered creating a Corporate Contacts table, but
that seems to me to break normalization rules, as it would essentially
contain the same information as customers, and someone's role may shift
between that of a contact and that of a customer.

I have also considered creating a junction table to connect customers to
companies, but that doesn't seem to make a lot of sense, either, as it seem
that the relationship should be many-to-one; not many to many.

Any suggestions?

Thanks in advance,
Tim
 
R

rico

What is the relationship (in real world terms) between a customer, a contact,
and a company? Why are they different? sureley if they are placing an order,
are a customer. Why do they need different names?

Rico
 
A

Allen Browne

Tim, take a look at the structure suggested in this article:
People in households and companies - Modelling human relationships
at:
http://allenbrowne.com/AppHuman.html

The basic idea is to put companies and individuals in the one table of
"clients", and use related tables to connect the individuals with the
companies. You can then sell to any kind of client, receive payments, and so
on.

There's a downloadable sample database to play with.
 
T

Tim Johnson

Allen, as usual, your solution is genius!

I had toyed with all sorts of variations, but hadn't been able to pin down
anything nearly as simple, nor elegant! Thanks for your help.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top