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?