I'm in the company is advertising agency, advertising company to make money is by (get discounts from the downstream media & gt; To upstream customers discount) to earn price difference,
Now I want to design a database to satisfy their needs of statistical business:

Business relation is roughly like this:
Internal:
1) A customer ID will have A sales ID (employees) to follow up, but there will be A ZhuanHu situation, that is to say 19 years A is follow up customer 20 annual meeting by B,
2) a customer will by multiple vendors to open accounts in multiple media, a media also will have more than one account in the above on the
3) the client side: media is different, have different industry is different, we give customers a discount of
4) media side: and the client side, the customer is different, have different industry, different media to give our discount
5) need to be statistics per day per delivery account daily consumption

Want to ask next:
A. 1) also calculate many-to-many relationship? In addition to build the mapping table has other design method is not?
B. I don't know the main problem is that to give customers a discount of discount and to the media on which table, is to build a customer a discount list separately and media discount table or is directly recorded on the daily data on this list?
C. if it is a separate discount separate table up, like this customer divisions points, how to draw diagrams media policy?


Here I drew to the upstream customers discounts, but from the downstream of the media that I don't know how to draw the discount policy,
Or should have the customer side discounts and media side discounts are recorded in the mapping table?


D. because of the discount policy from the media side will change over time, so you need to record history discount, my idea is to input data when matching policy write die at that time, I don't know is there any better way?
E. due to historical statistics on the data, so the consumption data like this day, people usually is how to record? Order is as a record or a joint account of the time and do primary key record?

CodePudding user response:

A: customer data table and personnel data sheet is separate to design, it can realize A many-to-many relationship, such as A \ B \ C customers, zhang SAN is over, can adjust at any time, including customer give li si, C
To say is that a many-to-many relationship, involving the multipoint trigger business requirements, with foreign key constraints in appearance, etc.

B: the amount of discount is to belong to, it has two design factors,
1. Access logic is according to the level of customer or order amount of level to calculate the discount?
2. According to the customers' questioning good discounts, set?

If relationship is 1, then divided the customer credit assessment and order discount base table to contract its algorithm logic,
If a relationship is 2, only in the customer information table, plus the discount base, as evaluation calculation parameters,

C. if the customer at the same time of planning, to consider + media classification according to the industry, that some of the hierarchy should be:
Supply data table: (media unit)

Industry type ID (suggest a system on the basis of classification table)
Media type ID based classification (in)

In this framework to build,

D. media discount policy is dynamic, you have to consider, is a dynamic, according to the time or the contract signed between them to happen,
If by the time, no matter according to the year, quarter, month, can set a list of accounting payment days (the time range object) with the table checked discount base, can calculate the amount of concrete,

According to the contract, can only consider writing on the front end, depending on how to implement the business content contract, find the rule to see

CodePudding user response:

More historical data sent back to check, the following terms:
1, it is by business type back to check, or by what way, such as time, customer + product type, etc.
2, there are many types of implementation way, written procedure (stored procedure) front end through transfer (specific conditions) make calls, returns to the front back to check,
Also can write method to front-end call directly,
3, data back to check, retroactive last (previous) several, also has by the number of set time range, more complex, dynamic parameters which can be considered as a business parameter table, stored in the database, the front-end can configure these parameters,
With the corresponding functions or stored procedures can be implemented,