Designing Home Page Recommendation Tables: SQL Schemas Explained

Related Table Structures

Home Brand Recommendation Table

Used to manage brand direct‑supply information displayed on the homepage.

<code>create table sms_home_brand (
   id bigint not null auto_increment,
   brand_id bigint comment 'Product brand id',
   brand_name varchar(64) comment 'Product brand name',
   recommend_status int(1) comment 'Recommend status: 0->not recommended;1->recommended',
   sort int comment 'Sort order',
   primary key (id)
);</code>

New Product Recommendation Table

Used to manage fresh product information displayed on the homepage.

<code>create table sms_home_new_product (
   id bigint not null auto_increment,
   product_id bigint comment 'Product id',
   product_name varchar(64) comment 'Product name',
   recommend_status int(1) comment 'Recommend status: 0->not recommended;1->recommended',
   sort int comment 'Sort order',
   primary key (id)
);</code>

Popular Product Recommendation Table

Used to manage popular recommendation information displayed on the homepage.

<code>create table sms_home_recommend_product (
   id bigint not null auto_increment,
   product_id bigint,
   product_name varchar(64),
   recommend_status int(1) comment 'Recommend status: 0->not recommended;1->recommended',
   sort int comment 'Sort order',
   primary key (id)
);</code>

Homepage Subject Recommendation Table

Used to manage curated subject information displayed on the homepage.

<code>create table sms_home_recommend_subject (
   id bigint not null auto_increment,
   subject_id bigint comment 'Subject id',
   subject_name varchar(64) comment 'Subject name',
   recommend_status int(1) comment 'Recommend status: 0->not recommended;1->recommended',
   sort int comment 'Sort order',
   primary key (id)
);</code>

Homepage Advertisement Table

Used to manage carousel advertisement information displayed on the homepage.

<code>create table sms_home_advertise (
   id bigint not null auto_increment,
   name varchar(100) comment 'Name',
   type int(1) comment 'Carousel position: 0->PC;1->App',
   pic varchar(500) comment 'Image URL',
   start_time datetime comment 'Start time',
   end_time datetime comment 'End time',
   status int(1) comment 'Online status: 0->offline;1->online',
   click_count int comment 'Click count',
   order_count int comment 'Order count',
   url varchar(500) comment 'Link URL',
   note varchar(500) comment 'Remark',
   sort int default 0 comment 'Sort order',
   primary key (id)
);</code>

Admin View

Mobile View