Many to many relationship

Question

I have a customers and managers, two tables independently. My customer table have nearly hundred million records whereas manager table have 100 records. Now i am in position to map the customers to manager. Rules are as follows

1. One manager can have multiple customers.
2. One customer may mapped with multiple managers.

What is the best DB Design to solve this? Create able ManagerCustomerMapping is one idea. But i am not happy with it. because of this lead me a very big table. For example. If the Manager1 and Manager2 mapped with all customers then this table have 2 hundred millions of records.

Answer 1

The best DB design, despite your misgivings, is exactly what you described. In other words, have a mapping table ManagerCustomerMapping.

Always start with 3NF and modify if and only if there are real performance problems that can't be solved in other ways.

If your business is as big as it looks (with 100 million customers), disk storage should not be a problem, and proper indexing of the mapping table should mitigate any performance concerns.

And yes, if every customer maps to two different managers, you will have 200 million records. That's not a problem. On the sort of shops I work in (DB2 on System z), that's about a medium-sized table.

The beauty of SQL is that you can mostly swap out a DBMS if it doesn't perform well enough.

Two hundred million rows of two ID columns would not be onerous to the average database, and this is the best way to go, especially if there's the possibility that a customer may not be allocated to a manager (or vice versa). Any other solution that tries to put a customer ID into the manager table (or a manager ID into the customer table) will waste space in that case.