Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Databases are not my primary skill and I am finding I need some assistance please.

The software we are building has "Customers". The Customer could either be a Person or an Organisation.

I really want to create an optimal schema for this.

I have these considerations.

  1. A Person can have one or more Contacts (eg. phone, email)
  2. A Person can have one or more Addresses
  3. An Organisation can have one or more Contacts (eg. phone, email)
  4. An Organisation can have one or more Addresses
  5. An Organisation can have one or more Persons related to it.

I would like the App to scale decently, so the schema should fit this choice.

I am trying to achieve something like the following.

SELECT * FROM Customers + a few joins.

1. Person | NULL | John Doe | Primary Organisation Contact | Primary Address

2. Organisation | Acme Ltd | Jane Doe | Primary Organisation Contact | Primary Address

I would really appreciate some direction, help, assistance on how i should create an optimal schema for relating the above.

I have attached a rough Visual Schema hierarchy - I know im way off!! and Im sure im making errors.

http://snag.gy/yshxE.jpg

Is it possible to get a married set of results depending on whether its a Person or Organisation?

Joining a Person and Contact/Address to Customer is simple, but How do you join Organisation's primary Contact/Address ?

Is there any easier way to achieve my spider-diagram looking Schema.??

share|improve this question

migrated from stackoverflow.com May 2 '13 at 17:16

This question came from our site for professional and enthusiast programmers.

    
if people and organization inherites from customer, try to make customer contact and customer address. –  AnatolyS May 2 '13 at 15:54
    
One approach is to say that everybody must belong to an organization - even if that organization is 'them_self' or 'their_house'. This is the approach that I use in our projects database. Sometimes clients come to us as private individuals rather than as large organizations (although they may also belong to large organizations). In this this instance they are registered as belonging to the 'client group' for that project. Does that make sense? –  Strawberry May 2 '13 at 16:39
    
Thats an interesting point Strawberry. What if the Client is a private individual today and tomorrow comes to you as the primary contact at a company. This is a very real scenario for us. –  user492681 May 2 '13 at 16:41
add comment

4 Answers 4

Some insights from having actually built a couple of apps with contacts and companies.

Firstly, you're missing several use-cases in your outline. Among the colorful ones I've run into over the years which you don't necessarily cover:

  1. Some organizations have sub-organizations, be them divisions, subsidiaries, you name it.
  2. Some people belong to several organizations. As in VP whatever of XYZ, chairman of ABC, and self-employed CEO/consultant -- each on a part time basis, hopefully.
  3. Some people can be contacts for an organization without actually belonging to it. E.g. when a high profile consultant is temporarily hired as a project lead, he could possibly become the primary contact for company XYZ without actually belonging to it.
  4. People can have sub-people too. And the latter can be in the same companies, or not. For instance, our VP of XYZ might prefer that you go through his main primary secretary over there, unless you contact him regarding ABC in which case that would be his assistant; or personally for consulting work -- a very busy person indeed, but there are people like that.
  5. Some organizations have no contacts associated with them. This can happen, for instance, in an app that outputs lists prospect companies (for sales people) in which contacts have yet to be identified.
  6. Conversely, contacts can have no organizations. For instance consumers.

Secondly, since your outline mentions customers, be wary that customers can be a company or a (physical) person. You seem to have that part covered based on your diagram, so let's move on.

If your next step is to add anything related to accounting, such as an "orders" table, be wary that anything accounting related is tied to a company, a contact, products, prices, etc. at a given point in time. These details can evolve in all sorts of colorful ways, and a frequent design mistake is to create the perfect normalized design and assume that you'll just update the tables as needed. Big no-no. If the taxman asks you to print out your invoices, and your IT says company XYZ at price P when accounting booked company ABC at price Q, you're very, very screwed. And don't get me started on archived/closed yearly accounts and reports that change due to similar design mistakes.

Thirdly, be very, very, very wary of UI/UX issues that you might introduce by normalizing too much. If it does not work like the apps that typical users have in their hands (read: Outlook), you'll be a poorly trained secretary away from changing the company of every employee at XYZ to ABC when Joe gets a new job. I've actually seen this happen and it was not a pretty sight. (She kept her job, in case you're wondering.)

Lastly, don't get me started on merging the inevitable duplicate companies, contacts, and so many other colorful things that couldn't possible turn up. Keep those in mind and make sure that your schema is very, very forgiving because it will happen.

Now... in practice...

Personally, I've come to actually turn a blind eye on normalization in this case. Contacts/Companies is one of those cases where DB design and beautifully normalized data as taught in school is a recipe for trouble in the form of resource hogging, over-complicated queries and ludicrously complex UI. It is, imho anyway, not the right thing to do.

Start by creating a contacts table, complete with fields such as first and last name, display_name if needed, company_name, address, phone, cell_phone, email, email2, secretary_name, secretary_phone, etc. If it's available when you create a contact in Outlook, it probably belongs in there.

You'll note that I didn't mention a companies table. That's because you usually don't want any strong ties between your contacts table and your companies table. If you need one, add one, and add a copmany_id in addition to the company_name. But then, make it a foreign key on delete set null. And be sure to keep it very, very loosely tied to company_name at the database level. Maintain it at the front-end level -- not anywhere else. This will keep a secretary from inadvertently changing the company of multiple contacts.

Keep things sane. Anything that might reasonably show up in a list (i.e. select * from contacts where ... limit 10), be queried against, or be useful frequently ought to be in the table. No joins, nada. Query, iterate through the result, done.

If you've really want additional garbage in there, you've two options.

One is to create an extra_contact_details table. It can be an EAV table, or the full load of company_name, address, phone, etc. fields, or a hogwash of normalized bits and pieces. Whichever option you take, don't over do it. Either will create additional (potentially complex) queries, and heaps of thorny programming issues down the road when you create the UI. What's absolutely key here is, if you go this route, a secretary who has always worked with Outlook needs to make sense of it.

The other, which I'll actually recommend upfront, is to add a text field called "extra_contact_details" in the contacts table and begone with it. No normalization at all. One set of primary details. Frequently used secondary details. Anything extra as plain text. Secretary gets it. End users get it. Done.

Lastly, if you need to store a version of any data at a given point in time, be sure to duplicate the value of any key details -- read: whatever you need to print it exactly as it was when it got inserted.

share|improve this answer
1  
Could you tell a thing or two about how a normalized/denormalized database schema could affect the UI/UX? –  dezso May 2 '13 at 18:09
    
One big example would be when you normalize the company. If you toss in a link to a screen with an edit button when you click it, you may very well end up with the secretary screw up I mention further up. One alternative, which is a convenience textfield to rename the company, amounts to the same. The last alternative is to have a dumb text field with no ties to the table at all (just like in Outlook), and to tie things back up in a subsequent (or ajax) screen if there is actual need for normalization. –  Denis May 2 '13 at 18:16
    
I don't get it. Do you mean an Access database? I think no, as (AFAIK) there is no Ajax in Access. Then why couldn't a middle layer (I mean some logic between the frontend and the database) deal with input validation? I know it is not trivial, but it is far from being impossible. –  dezso May 2 '13 at 18:24
    
I'm not saying it's impossible. Merely that it's thorny. And that in practice there's very little value in doing so "properly" considering the joins, validation issues, and UI/UX involved, vs something that would actually cover 99% of cases with a conveniently well known UI, aka Outlook. –  Denis May 2 '13 at 18:33
add comment

Take a look at the Party Data Model, it should meet your requirements.

http://www.tdan.com/view-articles/5014/

share|improve this answer
add comment

What about something like this?

addresses
    id              unsigned int(P)
    street1         varchar(50)
    street2         varchar(50)
    city_id         unsigned int(F cities.id)
    zip             varchar(6)
    lat             double
    lon             double

cities
    id              unsigned int(P)
    state_id        unsigned int(F states.id)
    name            varchar(50)

countries
    id              char(2)(P)
    iso3            char(3)(U)
    iso_num         char(3)(U)
    name            varchar(45(U)

customers
    id              unsigned int(P)
    parent_id       unsigned int(F customers.id) (Allow NULL)
    name            varchar(50) // John Doe, ABC Company, etc.

customers_addresses
    id                  unsigned int(P)
    customer_id         unsigned int(F customers.id)
    address_id          unsigned int(F addresses.id)

customers_email_addresses
    id                  unsigned int(P)
    customer_id         unsigned int(F customers.id)
    email_address_id    unsigned int(F email_addresses.id)

customers_phone_numbers
    id                  unsigned int(P)
    customer_id         unsigned int(F customers.id)
    phone_number_id     unsigned int(F phone_numbers.id)

email_addresses
    id              unsigned int(P)
    address         varchar(255)

phone_numbers
    id              unsigned int(P)
    area_code       char(3)
    exchange        char(3)
    station         char(4)
    extension       varchar(10)

states
    id              unsigned int(P)
    country_id      char(2)(F countries.id)
    code            char(2)

Phone numbers and zip codes are geared towards north American countries and would have to change for other countries.

Here's some sample data for the customers table:

+----+-----------+---------------+
| id | parent_id |     name      |
+----+-----------+---------------+
|  1 |      NULL | ABC Company   |
|  2 |      NULL | XYZ Company   |
|  3 |         1 | John Doe      |
|  4 |         1 | Jane Doe      |
|  5 |         2 | John Q Public |
|  6 |      NULL | JKL Company   |
+----+-----------+---------------+

Organisations always have a NULL parent_id. Individuals could have a NULL parent_id (when they aren't associated with an organisation) or non-NULL parent_id (when they ARE associated with an organisation). Your specific needs might require other changes...

Example SQL:

SELECT * FROM customers c
LEFT JOIN customers_addresses ca ON c.id = ca.customer_id
LEFT JOIN customers_email_address ce ON c.id = ce.customer_id
LEFT JOIN customers_phone_numbers cp ON c.id = cp.customer_id
LEFT JOIN cities ON ca.city_id = cities.id
LEFT JOIN states ON cities.state_id = states.id
LEFT JOIN countries ON states.country_id = countries.id
WHERE c.id = 1
share|improve this answer
add comment

Man, I wish database books would cover the Party Model in the first chapter.

If your time is worth much to you, read Enterprise Model Patterns by Hay or Data Model Resource Book by Silverston. Can find em cheap on Safari.

Also read up on Table Inheritance:

http://martinfowler.com/eaaCatalog/singleTableInheritance.html

If you don't like nulls then use Class Table Inheritance.

Party represents People, Organizations, Automated Agents:

Party
 id
 name

Individual : Party

Organization : Party

AutomatedAgent : Party

A customer is a role played by a party. For example, someone could be your supplier, you hire them as an employee, fire them, and then they become your customer.

You could derive their "customerness" from whether or not they have placed a sales order with you, or declare them as customers using PartyRole.

PartyRelationship represents relationships between parties:

PartyRelationship
 fromParty
 toParty
 fromDate
 toDate (nullable)

OrganizationContact : PartyRelationship

Employment : PartyRelationship

Address represents physical and virtual addresses:

Address
 id

EmailAddress : Address
 emailAddress

WebAddress : Address
 url

TelephoneNumber : Address
 telephoneNumber

MailingAddress : Address
 suiteOrApartment
 street
 city
 zip

People have many contact methods:

ContactMethod
 party
 address
 fromDate
 toDate (nullable)
 telephoneExtension (nullable)

I would strongly recommend using a tool like Hibernate, as it will make your life much easier.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.