Geek Answers Handbook

From many to many

I have clients and managers, two tables independently. My client table has nearly one hundred million records, while the manager table has 100 records. Now I am able to match clients with a manager. The rules are as follows

  • One manager can have several clients.
  • One client can be displayed by several managers.

What is the best database design to solve this problem? Create a capable manager CustomMapping is one of the ideas. But I am not happy with that. because it led me to a very large table. For example. If Manager1 and Manager2 are mapped to all clients, then this table contains 2 hundreds of millions of records.

+5
source share
3

, , - , . , ManagerCustomerMapping.

3NF , , .

, ( 100 ), , .

, , 200 . . , (DB2 on System z), .

SQL , , .

ID , , , ( ). , ( ), .

+10

. - 100? , 1M? ? , , (DW), , Kimball :

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, ..)

factSales , , , , , . - , -.
, ?

0

. , ? ? , - .

↔ , , ( "--" ) . , , , , , , ( , ).

​​ , ( ).

If this is the case, you need to express the relationship between the managers separately (either with a self-referencing column in the managers table, if for each manager there can be only one direct parent manager, or a separate table if this is a lot for many) and only connect customers to their final one, direct manager.

0
source

More articles:


All Articles