I'm trying to design a relatively simple ERP system. However, there are some requirements that complicate things a little bit:

  1. It must be possible to add all sorts of contacts to the people table, including clients and co-workers.
  2. It must be possible to assign a user to a contact, so users can access their schedules and stuff.
  3. It must be possible for users to be assigned to multiple customers, when for instance a user works for several organisations.
  4. It must be possible for different organisations to have different contact details for one user.
  5. When — in the future — a project management functionality is added, it must be possible to share projects between organisations.

I came up with this simple data model:

Data model

As you can see, there is some data duplication between tables.

Should I just just get rid of the customer's organisation name, and retrieve that from the customer's contact field instead? And yes, the customer's contact is the person that receives invoices and such from us. Is this a good design decision or should I not use the people table for this?

The user's name is a duplication of the contact's name, but I don't think this is avoidable? I don't want to tie the user's details to the contact's details, see point 4.

Again, this is just a very simple 'mockup' to visualise things, but what kind of improvements can I make to this model? Is there a more elegant way?

  • Data duplication is A Bad Thing en.wikipedia.org/wiki/Third_normal_form Feb 20 '15 at 15:13
  • Most definitely, but I'd like to keep things separate from each other, such as user data and contact (CRM) data. Is data duplication allowed in these instances? Feb 20 '15 at 15:16
  • 1
    Data duplication is not always a bad thing, decoupling might be sometimes a serious reason to duplicate data when having careful designed synching mechanisms. But when designing a business model for an ERP system from the ground, as shown here, I would avoid any redundancy as hell. And do yourself a favor, read martinfowler.com/apsupp/accountability.pdf first before reinventing the wheel.
    – Doc Brown
    Feb 20 '15 at 15:36
  • 3
    As someone who has actually worked as a DBA on RDBMs with billions of rows, denormalisation is sometimes a necessary evil for performance reasons. However, that is primarily an implementation concern (what we used to called physical design). You should aim to eliminate any data duplication in the initial (logical) design.
    – Robbie Dee
    Feb 20 '15 at 16:38
  • 1
    Could you substitute that mind map with a Crow's foot ER diagram? Example: upload.wikimedia.org/wikipedia/commons/9/91/… Feb 21 '15 at 0:26

TL;DR; Normalization is a good approach and if you have problems with that it may be sign there's an issue with your approach - try redesign the problem.

I would normalize this as much as possible (and reasonable) because that what leads to better design in general. For example your 'people' table looks more like 'contact_info' table, if that's true - it probably doesn't need a name.

Then: Organization probably needs a name, and person (a.k.a. customer?) also have one because it's something different.

Lastly: Users probably don't need name, but login (or e-mail if it can be used as login - which I think is a good practice because it's easier to remember it and you don't have to deal with already taken usernames) and password is needed.

With such design customer can belong to one or many organisations (in the latter - you need extra table for that connection), and organization can dafault_contact which is FK to contact_info.id or customers.id (you're the one to decide what makes more sense in this case).

With such aproach there's no 'people' table which accepts organizations, customers and actual people (this is confusing) - you name your tables by what they actually contain: contact_data, person (personal data), company (tax id, website url. etc), users, and if needed customers (see below).

Questions for spacifying customers table - can customer be only company or also private person? - can customer be a company without any person assigned to it?

Answers could help you determine if you need 'is_customer' in person/organization table OR customers table with FK person_id or organization_id or both.

  • Great answer. I'm trying to design a system for both freelancers and small organisations. My initial thought was to use one table (people) for both contacts (customers, suppliers, etc.) and employees. However, I'm not quite sure if that's a wise thing to do. Any opinions? The next problem I need to deal with is authorisation; who can access what parts of the system. Can managers of one department see documents of other departments and so on. Should I implement this based on roles or per individual user? Should the system offer flexibility or should some things be hardcoded, etc. Feb 22 '15 at 13:18
  • Like they say: "There are only two hard things in Computer Science: cache invalidation and naming things". Try to ask yourself if name of "the thing" is accurate (person is not commom set of attributes for companies and users, but contact_details could be). Your initial thought was ok - but you didn't name it correctly. You can try to describe relations in belongs_to, has_one, has_many and has_and_belong_to_many terms (in relational design you would see it as 1-n, n-n relations). For example: person has_many contact details, person has_and_belongs_to_many (n-n) organisations.
    – Grzegorz
    Feb 22 '15 at 15:38
  • As for auth: this is a whole topic in itself. There's probably by-resource auth (admin can access /organisations/list) and by-ownership auth (user can access /users/X/files only when X is his ID). You can easily put ownership info (just user_id foreign key in files table), but rest is probably more easily handled in App layer - this is the way I'm used to do it. If your DB engine allows handling Auth - I can't help you because I've never did it that way.
    – Grzegorz
    Feb 22 '15 at 15:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.