I’m trying to design a relatively simple ERP system. However, there are some requirements that complicate things a little bit:
- It must be possible to add all sorts of contacts to the people table, including clients and co-workers.
- It must be possible to assign a user to a contact, so users can access their schedules and stuff.
- It must be possible for users to be assigned to multiple customers, when for instance a user works for several organisations.
- It must be possible for different organisations to have different contact details for one user.
- 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:
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?
7
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.
3