Tweeted twitter.com/#!/StackProgrammer/status/568821076207280128
3 added 47 characters in body
source | link

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?

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.
  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?

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?

2 added 168 characters in body
source | link

I'm trying to design a relatively simple CRMERP 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.
  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?

I'm trying to design a relatively simple CRM 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.
  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?

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?

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.
  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?

1
source | link

Should I avoid data duplication?

I'm trying to design a relatively simple CRM 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.
  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?

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?