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.

Answer 2

Your numbers are quite intriguing. How many customers can an account manager know -- 100? How many managers do you have, 1M? Would a sales person be a better description? If so, maybe you should consider data warehouse (DW) approach, for example a Kimball star would look like:

TABLE dimCustomer (KeyCustomer, Name, Address, ...etc)
TABLE dimSalesPerson (KeySalesPeson, Name, Phone, Area, ...etc)
TABLE dimProduct (KeyProduct, Description, CatalogPrice, ...etc)
TABLE dimDate (KeyDate, FullDate, Year, Month, DayOfWeek, IsHoliday, etc...)
TABLE factSales (KeyCustomer, KeyProduct, KeySalesPerson, KeyDate, Quantity, Ammount, OrderID, ..)

The factSales table would capture sales of every item, admittedly large table, but you would not need to map customers to managers at all, simply search the fact table and find the last sales person who had contact with the customer. Somehow I think that this may be closer to the business model.
If it's not a secret, what kind of business is this database tracking?

Answer 3

Now hold on. You state that a manager might be assigned to ALL the customers? A manager might be responsible for a hundred million customers? Honestly, it sounds like something's wrong there.

If you have a simple manager <-> customer relationship as described, then the design you described (a many-to-many linking table) is correct. But if you really want to be able to link ALL the customers to several of the managers, I'm guessing that there's a hierarchy of managers that you haven't told us about — that is, a manager can manage other managers, who can manage other managers, who then manage customers (with additional levels possible and direct management of customers mixed in with management of managers at any level).

You see this kind of structure in multi-level marketing organizations and also in commission systems in certain industries (I just happened to come across it in insurance the other day).

If that's the case, you need to express the relationship among the managers separately (either with a self-referential column in the managers table, if there's only one direct parent manager possible for each manager, or a separate table if it's many to many) and only link the customers to their ultimate, direct manager.