How to design the mall's advertising table structure in MySQL?

How to design the mall's advertising table structure in MySQL?

When designing the advertising slot table structure of the mall, we should consider the attributes and relationships of the advertising slots to meet the operational needs of the mall website. Here's an example of a basic slot table structure design to help you achieve this goal.

1. Advertising table (ad_position)

Table name: ad_position
Fields: position_id, position_name, position_desc, create_time, update_time

Explanation:

• position_id: Advertising slot ID, unique identifier, as primary key
• position_name: Advertising slot name, used to display or identify the advertising slot
• position_desc: Advertising slot Description, which can describe the specific location of the advertising space or other related information
• create_time: the creation time of the advertising space, records the creation time of the advertising space
• update_time: the update time of the advertising space, records the last time of the advertising space Update time

2. Advertising table (ad)

Table name: ad
Fields: ad_id, ad_name, ad_position_id, ad_image_url, ad_link_url, sort_order, start_time , end_time, is_enabled, create_time, update_time

Explanation:

• ad_id: Advertising ID, unique identifier, as primary key
• ad_name: Advertising name, used for display Or identify the advertisement
• ad_position_id: Advertising slot ID, the foreign key is associated with the position_id field of the ad slot table
• ad_image_url: Advertising image address, used to display advertising images
• ad_link_url: Ad link address, when you click on the ad, you will jump to the link
• sort_order: ad sorting order, used to control the display order of ads
• start_time: start time of the ad, control the display time of the ad Segment
• end_time: The end time of the ad, control the display time period of the ad
• is_enabled: Whether the ad is enabled, you can control the display or hiding of the ad
• create_time: Ad creation time , record the creation time of the advertisement
• update_time: advertisement update time, record the last update time of the advertisement

The sample code is as follows (based on MySQL):

CREATE TABLE ad_position (
position_id INT(11) NOT NULL AUTO_INCREMENT,
position_name VARCHAR(100) NOT NULL,
position_desc VARCHAR(255),
create_time DATETIME NOT NULL,
update_time DATETIME NOT NULL,
PRIMARY KEY (position_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE ad (
ad_id INT(11) NOT NULL AUTO_INCREMENT,
ad_name VARCHAR(100) NOT NULL,
ad_position_id INT(11) NOT NULL,
ad_image_url VARCHAR(255) NOT NULL,
ad_link_url VARCHAR(255) NOT NULL,
sort_order INT(11) NOT NULL DEFAULT '0',
start_time DATETIME,
end_time DATETIME,
is_enabled TINYINT(1) NOT NULL DEFAULT '1',
create_time DATETIME NOT NULL,
update_time DATETIME NOT NULL,
PRIMARY KEY (ad_id),
CONSTRAINT fk_ad_position_id FOREIGN KEY (ad_position_id) REFERENCES ad_position (position_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

The above code creates two tables, ad_position and ad, which are used to store advertising slots and advertisements respectively. Related Information. A foreign key association is established through the position_id field of the ad_position table and the ad_position_id field of the ad table.

This table structure design can be used for a simple mall website to meet basic advertising display and management needs. Of course, depending on specific business needs, the table structure may be adjusted or expanded. Hope this helps!

The above is the detailed content of How to design the mall's advertising table structure in MySQL?. For more information, please follow other related articles on the PHP Chinese website!