表头有日期,计划id,计划名称,单元id,单元名称,商品id,花费,点击,计划id和计划名称是一对一的关系,计划id和单元id是一对多的关系,单元id和商品id是一对一的关系,每日每个计划id对应的单元id和商品id会产出花费是常用数据、点击是不常用数据,该怎么搭建数据模型并写成mysql代码
时间: 2023-03-28 18:03:15 浏览: 84
可以使用以下的数据模型:
计划表(plan):
- plan_id (计划id)
- plan_name (计划名称)
单元表(unit):
- unit_id (单元id)
- unit_name (单元名称)
- plan_id (计划id)
商品表(product):
- product_id (商品id)
- unit_id (单元id)
数据表(data):
- date (日期)
- plan_id (计划id)
- unit_id (单元id)
- product_id (商品id)
- cost (花费)
- clicks (点击)
可以使用以下的mysql代码来创建这些表:
CREATE TABLE plan (
plan_id INT PRIMARY KEY,
plan_name VARCHAR(255)
);
CREATE TABLE unit (
unit_id INT PRIMARY KEY,
unit_name VARCHAR(255),
plan_id INT,
FOREIGN KEY (plan_id) REFERENCES plan(plan_id)
);
CREATE TABLE product (
product_id INT PRIMARY KEY,
unit_id INT,
FOREIGN KEY (unit_id) REFERENCES unit(unit_id)
);
CREATE TABLE data (
date DATE,
plan_id INT,
unit_id INT,
product_id INT,
cost DECIMAL(10,2),
clicks INT,
PRIMARY KEY (date, plan_id, unit_id, product_id),
FOREIGN KEY (plan_id) REFERENCES plan(plan_id),
FOREIGN KEY (unit_id) REFERENCES unit(unit_id),
FOREIGN KEY (product_id) REFERENCES product(product_id)
);