0

I'm designing the database schema for a CRM. It has the following tables: users, organizations, contacts, addresses, organization_types.

organizations might be a company, a non-profit or a individual.

users have a many to many relationship with organizations. The reason is because one company, for example, can have a salesman and a manager on the app with two different logins. On the other hand, there is the possibility that a salesman is doing sales for two different companies.

contact and addresses have a one to one relationship with organizations. My client only want a organization to have one of each of those.

I designed it in a way that a organization can also be a client, that would belong to another organization. That would mean that the organization table had a one to many relationship with itself.

That made sense to me because they seem to be the same entity. A client will also need a contacts and addresses table and it could also be a company, non-profit or individual.

The point to consider that another developer raised is that with time, it would be expensive to query the database to differentiate organizations that are our clients from the ones that are clients from our clients.

What is the best approach? Would it be better the make a client_organizations table and separate those two?

Marcos Almeida
  • 25
  • 5
  • 1
    Are you asking about doing hierarchical queries? (organization is-client-of organization is-client-of organization is-client-of organization etc.) – O. Jones Jan 20 '17 at 18:36
  • @O.Jones I just want to know if that aproach will be too costly to make queries. To differentiate which organizations are subscribed to use my CRM application and which ones are their clients, just registered there on our product. Would it be better the make a `client_organizations` table? – Marcos Almeida Jan 20 '17 at 18:46
  • 2
    I would have only one organisations table – Strawberry Jan 20 '17 at 18:51
  • See http://stackoverflow.com/a/5471265/562459 – Mike Sherrill 'Cat Recall' Jan 20 '17 at 19:11
  • I would split them, especially if uber-admins edit orgs and org admins edit their clients. In that scenario, there's no real savings of data entry effort or space savings. You'd just be making the reporting more complex. If you're worried about implementing a real-world model with recursion, you'll add org relationship table(s), but then you'll run in to the need to add random-appearing permissions restraints on some records editing. I'm assuming you won't be doing that, and that's really the only reason you'd _need_ to put them in the same table. – GumbyG Jan 20 '17 at 19:27
  • I'm not sure, but maybe clients is a subclass of organizations. If so, you might want to look into class-table-inheritance. If not, don't worry too much about performance issues if you only have about 1000 organizations or less. That's a small table in today's world. – Walter Mitty Jan 20 '17 at 19:36

1 Answers1

0

I will keep one table and create a column named parent_organization. Parent_organization will be nullable and store primary key of parent organization that child organizations belong to

Quan Le
  • 11
  • 3