Recently, IdiLent's article "Use object-oriented technology to solve the product discount problem". After the text, readers have made requirements: What should I do if the discount of different goods is different? Or buy one hundred to send fifty ways, or different discounts of different membership levels. How to deal with? "IDilent thinks that it is not possible to solve the field through a database. Different members, different products, different sales plans, and these are constantly unchanged and increased. And products Discount, and the celebration, etc., although it is a discount, it is difficult to abstract a field or several fields in the database, not using the program to solve, and hope only by changing the data in the database, it may still more difficult.
I have participated in the project development of a DVR rental sales management system, responsible for the architecture design and data modeling, although the project was abandoned by some objective reasons, but there were discounts in this project. Demand, I also think about this piece of data modeling. In fact, we can break down the business rules such as product sales into several parts, analyze the relationship between the various parts, find the key points, and then generic data modeling, you can realize the user's own definition discount rules. . The following is the business rule for analyzing the product sales discount:
A commodity sales management information system must have the following two entities: customers, goods, and discount this business rule must be developed around these two entities and intermieration. Review our shopping experience, the demand for discount should be divided into three: 1) The discount for specific items generally has the following cases: a certain percentage discount on sale; the original price -> special offer (for a certain period of time) The discount; bundled offers (such as purchasing a certain (several) items, you can purchase another (several) items at a lower price or discount). 2) Generally use the membership system to use the membership system, that is, according to the membership level, directly gives a certain discount offer, or give a certain amount of return point after a certain amount of consumption, it takes customers. Apply the identity logo card in the membership card. 3) The discount method for the total amount is generally a voucher that defines the final use period after the customer's consumption.
It is noted that the three major discount methods are broken down, and they are inseparable from the goods, so these three discount methods are all the commodities of the goods, and should be classified into the product form. In addition, generally large supermarkets have multiple branches, and there may be a slightly different cases of discount rules in various branches, and the above three discount rules can be associated with the corresponding branch. Finally, data modeling is approximately as follows:
Shop Name Type Constrained Conditions SHOP_ID INT No Repeat Shop Identification, Primary Key Shop_name Varchar (40) Does Not Allows Name Shop_addr Varchar (80) Not allowed to be empty shop address ...
Commodity Category Table (Ware_TYPE) Name Type Constraint Condition Description Type_ID INT No Duplicate Category Identifier, Primary Key Type_name Varchar (20) Does Not Allow Empty Category Name Father Int Not Allows the parent category identifier of this category, if it is the top node Set to a unique value of Layer Char (6) Limited 3 floors, the initial value is the order traversal of 000000 category, mainly for retrieving the number of retrieval databases
Product Table (Ware) Name Type Constraint Condition Description Ware_ID INT No Duplicate Product Identification, Primary Key Ware_Type Int Does Not Allowed Category, and Ware_Type.Type_id Association Ware_Name VARCHAR (40) Does not allow the empty product name Buy_price float is not allowed Adding Sell_Price Float does not allow empty sales price D_TYPE CHAR (1) Do not allow empty product discount M_Type Char (1) Do not allow empty membership discount method HAS_COUPON bit default value is 0 Is there a voucher ... Description: 1D_Type To distinguish the product discount method of this product. "0" indicates that the product has no commodity discount; "1" indicates that the item uses a percentage discount method; "2" indicates that the item uses a special discount method; "3" indicates that the item is bundled, which is a bundled discount rule. Must purchase goods; "4" indicates that the item uses a bundled discount method and is the allowable item in the bundled discount rules. 2m_type is used to distinguish the membership discount method of the item. "0" indicates that the item does not participate in member discount calculations; "1" indicates that the item takes a percentage discount method; "2" indicates that the item takes a member card accumulated consumption rebate method. 3HAS_COUPON is used to indicate whether the item has a voucher. "0" indicates that the commodity is invisible; "1" is reversed.
Commodity Inventory (Store_Table) Name Type Constrained Condition Description STORE_ID INT No Repeat Inventory Identification, Primary Key Shop_ID INT does not allow empty shop identity, and shop.shop_id association ware_id int 不 is not allowed to be empty, and ware.ware_id association Number Int default Value 0 store stock quantity Unit varchar (10) does not allow empty sales units
Product Discount Rule Table (Discount) Name Type Constraint Condition Description ID INT No Repeated Discount Rules Identification, Primary Key S_ID INT does not allow empty shop identity, and shop.shop_id association W_ID INT does not allow empty merchandise ID, and ware.ware_id association D_Value Float does not allow an air discount value, used to record percentage or special offer enddate datetime does not allow the end date of this rule Number INT allows for the maximum sales allowed by this rule Unit varchar (10) Allow empty sales units
Product Bundle (BIND_DISCOUNT) Name Type Constrained Condition Description B_ID INT No Duplicate Bundle Discount Rules Identification, Primary Key Shop_ID INT does not allow empty shop identity, and shop.shop_id association 1st_ware int ignore the collection of merchandise items, And ware.ware_id association min_req int default is 1 minimum purchase quantity 2nd_ware int 不 不 不 不 集 集 集 集 集 标 联 联 联 联 联 联 联 联 联 联 联 集 集 集 集 集 集 不 联 不 购 联 联 联 数 联 联 1 Allow a discounted method, is a percentage or a special mode D_Value float does not allow a discount value to record the percentage or special offer enddate datetime does not allow the end date of this rule Number Int Allow the maximum sales allowed by this rule. Unit varchar (10) allows empty sales units Description: 1st_ware is used to record the collection of purchase items, and min_req represents the minimum purchase quantity within the purchase item collection. 2nd_ware is used to record the collection of goods, and max_buy indicates the maximum number of allowed items allowed to purchase the minimum purchase of the purchase item. For example: a bundled sales requirement, any one of the purchases in a series of products can be purchased at any one of the items in the purchase item. Everyone has seen this promotion? Buying two get one free is just the special case.
Membership Table (MEMBER_TYPE) Name Type Constraint Condition Description Type_ID INT No Repeated Member Identification, Primary Key S_ID INT does not allow empty shop identity, and shop.shop_id association type_name varchar (10) does not allow empty membership level name T_Value float is not allowed Condition float is not allowed to meet the amount of consumption required for empty discounts or cumulative consumer rebate
Member Table (MEMBER) Name Type Constraint Condition Description M_ID INT No duplicate member ID, the primary key m_name varchar (10) does not allow empty member name TYPE_ID INT does not allow empty membership rating ID, and Member_Type.Type_id association score float default value is 0 Member accumulated consumption points ...
Birage Table (Coupon) Name Type Constrained Condition Description C_ID INT No Duplicate Voucher ID, Master Keys C_Name Varchra (20) Does Not Allow Empty Voucher Name C_Value Float Not allowed to be empty in the amount of Condition Float does not allow for air required cash consumption Enddate DateTime does not allow end dates for empty vouchers
Of course, since I cognracted the discount method is not comprehensive, there is no inseparable problem with relevant business people. Therefore, the above data modeling does not guarantee all discounts in real-goods sales. However, I believe that the above data modeling is used to define the discount rule, the coverage is still more than 90%. According to the 95/5 rules, just give me enough time, coupled with professional assistance, if the development cost is not planned, 100% coverage can be achieved ^ - ^ Finally, because each shopping list is composed of goods And the calculation rules for each item will not be exactly the same, so I think that when designing the object-oriented design method, designing the decorative mode is more suitable.
