Location>code7788 >text

Counting System Design

Popularity：71 ℃/2024-10-23 09:14:31

There are three elements in the marketing scenario

  1. subscribers
  2. merchandise
  3. favorable

In these three elements, plus some other variables such as time, quantity, frequency, etc., will evolve a variety of combinations, making the business very flexible. In order to satisfy the business, each line of business will generally be implemented individually, and in most cases will be duplicated, and the implementation of each place will produce cross-configuration, cross-exclusion problem. After observing these problems, summarize and try to solve them uniformly with a system in the form of a counting frequency middleware. First, to solve the complexity, duplication of construction; second, after unified processing, the data level will be overall open, for user monitoring, wind control, characteristics, behavior to provide complete data.

In the system of e-commerce, there are three elements: goods, users, and offers. These three elements are further categorized in multiple dimensions in terms of quantity, time, and frequency, respectively.
If the product is limited to 100 copies (quantity) of the user per day (time) per person to buy a limited number of 2 copies (frequency), according to this law, the user to use the offer also follow this rule, that is, the
Total number of coupons 100 (quantity), users can use 1 (frequency) per session (time)
The same goes for the sweepstakes offers, i.e.
Total number of offers 100, users can receive 1 per day (time) (frequency)
The same applies to the issuance of concessions, i.e.
Total discount $100, 1 discount per day (time) for users (frequency)


taxonomy

  1. By Subject, Dimension
Main \Dimension quantities timing frequency
merchandise aggregate Specify period, days, hours, weeks Unlimited, 1 copy, multiple copies
subscribers aggregate Specify period, days, hours, weeks Unlimited, 1 copy, multiple copies
favorable aggregate Specify period, days, hours, weeks Unlimited, 1 copy, multiple copies
maneuver aggregate Specify period, days, hours, weeks Unlimited, 1 copy, multiple copies

According to this classification above, the subject will change and the dimensions will change, first design the domain model, respectively entity (entity); dimensions (dimensions); entity Dimensions Relationship (entityDimensionsRel), the relationship domain will indicate that the subject will have more than one dimension and set the dimension value in more than one dimension, for example, the product is limited to 1 copy per day, then it will be set in the entityDimensionsRel with 1 set in limit_value and time_interval set to 1 day

domain model

data model

database table model

  1. Entities
  • EntityID: Primary key that uniquely identifies a subject (e.g. product, offer, campaign, etc.)
  • EntityType: Subject type (e.g. product, offer, sweepstakes, etc.)
  • EntityName: Subject name
CREATE TABLE Entities (
    EntityID INT PRIMARY KEY,
    EntityType VARCHAR(50),
    EntityName VARCHAR(100)
);
  1. Dimensions
  • DimensionID: primary key that uniquely identifies a dimension (e.g., quantity, time, frequency)
  • DimensionName: Dimension name (e.g. "quantity", "time", "frequency")
  • DimensionType: Dimension type (e.g. integer, date, time interval, etc.)
CREATE TABLE Dimensions (
    DimensionID INT PRIMARY KEY,
    DimensionName VARCHAR(50),
    DimensionType VARCHAR(50)
);
  1. EntityDimensions
  • EntityDimensionID: primary key that uniquely identifies a subject-dimension relationship
  • EntityID: Foreign key, associated with the Entities table.
  • DimensionID: foreign key, associated with the Dimensions table
  • LimitValue: the limit value of the dimension (e.g., limit, limit, etc.)
  • TimeInterval: time interval (e.g. per day, per event, etc.), applies to the time dimension
CREATE TABLE EntityDimensions (
    EntityDimensionID INT PRIMARY KEY,
    EntityID INT,
    DimensionID INT,
    LimitValue INT,
    TimeInterval VARCHAR(50),
    FOREIGN KEY (EntityID) REFERENCES Entities(EntityID),
    FOREIGN KEY (DimensionID) REFERENCES Dimensions(DimensionID)
);

These tables above provide a complete record of the subject's limit values in each dimension. There is also a need for a table to record the process data of the subject data in each dimension in real time

  1. Instance Data Records Table (EntityDimensionRecords)
  • RecordID: primary key, uniquely identifies a record
  • EntityID: Foreign key, associated with the Entities table.
  • DimensionID: foreign key, associated with the Dimensions table
  • UserID: Foreign key, associated to the user table (when applicable)
  • Value: the value recorded in this dimension
  • RecordDate: The date or time of the record.
CREATE TABLE EntityDimensionRecords (
    RecordID INT PRIMARY KEY,
    EntityID INT,
    DimensionID INT,
    UserID INT,
    Value INT,
    RecordDate DATETIME,
    FOREIGN KEY (EntityID) REFERENCES Entities(EntityID),
    FOREIGN KEY (DimensionID) REFERENCES Dimensions(DimensionID)
);

Data flow

Sample 1

Users participating in the Big Wheel can only draw once during the event cycle

Main: Carousel event

Dimension: Time, Counting

Relationships: limit value (one time); limit time (activity period);

Simply fill in the data and the business is satisfied.

Now there is a new business requirement, i.e., this activity cycle is 1 month, users should be able to participate once a day, and the existing table model will not support it!

The time field TimeInterval of the relational table (EntityDimensions) is a varchar, which indicates that enumeration values such as 1 day, 1 hour, etc. are OK, but if it indicates a time period, complex times with frequency such as every day are not enough, so one of the best ways to do this is to split the time dimension out into a table

CREATE TABLE TimePeriods (
    TimePeriodID INT PRIMARY KEY,
    PeriodName VARCHAR(50),
    TimeInterval VARCHAR(50), -- time period (daily, hourly, half-hourly, minutely)
    StartTime TIME, -- time period start time
    EndTime TIME -- the time period end time
).

With this table, it is possible to represent the counting relationships for cycle time periods.

With this table, the business is querying EntityDimensionRecords and knows the period of this record, start time end time.

If the time frequency TimeInterval is days and the current time is 2022-12-12 when the value is 2022-12-11, then EntityDimensionRecords needs to regenerate a record and the RecordDate value is 2022-12-12;

The deeper you go, the dimensions that can be enumerated are limited, in the general direction, each dimension can also be split into a field is also feasible. Because the dimension values are different, simply using the dimension of a variable is a bit unable to meet the demand, such as the time dimension and the number of dimensions, the dimension value with a variable can not be expressed, the time range of at least 2 fields are needed. So in different periods of business development, only the most suitable design at that time, there is no always right design. While considering the scalability, you also need to consider the development cost.


If there is a need for a change in the way the counting is done in the future, such as a count consuming 2 chances, then accordingly, make a new table on the field to represent the complex way of counting. Everything changes, good design must support the business to grow up slowly. A bad design is one that keeps compromising the design, and will become more and more difficult to develop and maintain over time!

<Previous