智能家居数据库设计：传感器数据、场景管理、定时任务、消费记录、家庭成员权限

智能家居数据库设计

本文将介绍一个智能家居数据库设计，包含传感器数据、场景管理、定时任务、消费记录、家庭成员和权限管理等功能。

数据表设计

1. 传感器数据表

create table SensorData (
    DataID int primary key,--传感器数据ID
    DeviceID int foreign key references Device(DeviceID),--设备ID 外键关联到设备表
    SensorType varchar(50),--传感器类型
    SensorValue float,--传感器数值
    Timestamp DATETIME,--数据采集时间
);
insert into SensorData values(1, 1, '温度', 25.5, GETDATE()),(2, 2, '湿度', 50.0, GETDATE()),(3, 3, '光照度', 500.0, GETDATE()),
(4, 4, '二氧化碳浓度', 1000.0, GETDATE()),(5, 5, '噪音水平', 60.0, GETDATE()),(6, 6, '压力', 1001.2, GETDATE()),
(7, 7, '电流', 2.5, GETDATE()),(8, 8, '电压', 220.0, GETDATE()),(9, 9, '湿度', 45.0, GETDATE()),(10, 10, '温度', 26.0, GETDATE())

2. 场景表

create table Scene (
    SceneID int primary key,--场景ID
    SceneName varchar(50),--场景名称
    SceneDescription varchar(100),--场景描述
    UserID int foreign key references userb(UserID),--所属用户ID 外键关联到用户表
);
 insert into Scene values(1, '回家模式', '打开灯光、调节温度', 1005),(2, '离家模式', '关闭所有设备、锁门', 1001),
(3, '晚餐模式', '打开餐厅灯光、播放音乐', 1003),(4, '睡眠模式', '关闭所有灯光、调整温度', 1001),
(5, '读书模式', '调暗灯光、播放轻音乐', 1001),(6, '派对模式', '彩色灯光、高音量音乐', 1001),
(7, '工作模式', '调亮办公室灯光、提醒休息', 1001),(8, '早晨模式', '打开窗帘、播放清新音乐',1004),
(9, '健身模式', '调整房间温度、开始计时', 1001),(10, '放松模式', '调暗灯光、播放自然声音', 1001)

3. 场景设备关联表

create table SceneDevice (
    SceneDeviceID int primary key,--场景设备关联ID
    SceneID int foreign key references Scene(SceneID),--场景ID 外键关联到场景表
    DeviceID int foreign key references Device(DeviceID),--设备ID 外键关联到设备表
    StatusID int foreign key references DeviceStatus(StatusID),--设备状态 外键关联到设备状态表
);
insert into SceneDevice values(1, 1, 1, 1),(2, 1, 2, 2),(3, 2, 3, 3),
(4, 2, 4, 4),(5, 3, 5, 5),(6, 3, 6, 6),(7, 4, 7, 7),
(8, 4, 8, 8),(9, 5, 9,9),(10, 5, 10, 10)

4. 定时任务表

create table ScheduledTask (
    TaskID int primary key,--任务ID
    TaskName varchar(50),--任务名称
    TaskDescription varchar(100),--任务描述
    ExecutionTime time,--执行时间
    UserID int foreign key references userb(UserID),--所属用户ID 外键关联到用户表
    DeviceID int foreign key references Device(DeviceID),--设备ID 外键关联到设备表
    StatusID int foreign key references DeviceStatus(StatusID),--设备状态 外键关联到设备状态表
);
insert into ScheduledTask values(1, '早晨闹钟', '每天早上7点的闹钟', '07:00:00', 1001, 1, 1),
(2, '晚餐提醒', '每天晚上6点的用餐提醒', '18:00:00', 1001, 2, 1),
(3, '空调定时开关', '每天下午3点打开空调', '15:00:00', 1002, 3, 1),
(4, '音乐播放器', '每天晚上8点开始播放音乐', '20:00:00', 1003, 4,3),
(5, '温度调节', '每周五晚上9点调高温度', '21:00:00', 1004, 5, 5),
(6, '照明控制', '每天晚上10点关闭灯光', '22:00:00', 1005, 6, 9),
(7, '水泵定时', '每天中午12点启动水泵', '12:00:00', 1006, 7, 4),
(8, '净化器定时', '每天上午9点启动净化器', '09:00:00', 1007, 8, 6),
(9, '冷冻模式', '每周一至周五下午4点启动冷冻模式', '16:00:00', 1006, 9,2),
(10, '风扇定时', '每天晚上11点关闭风扇', '23:00:00', 1004, 10, 8)

5. 消费记录表

create table ExpenseRecord (
    RecordID int primary key, -- 记录ID
    UserID int foreign key references userb(UserID), -- 用户ID 外键关联到用户表
    Amount decimal(10, 2), -- 消费金额
    ExpenseTime datetime, -- 消费时间
);
insert into ExpenseRecord values(1, 1001, 100.00, '2022-01-01 09:30:00'),(2, 1002, 50.00, '2022-02-05 14:15:00'),
(3, 1003, 80.00, '2022-02-10 18:45:00'),(4, 1004, 120.50, '2022-03-03 11:20:00'),(5, 1005, 200.00, '2022-04-15 16:30:00'),
(6, 1006, 75.80, '2022-05-20 10:00:00'),(7, 1007, 150.00, '2022-06-08 13:45:00'),(8, 1008, 60.00, '2022-07-22 19:00:00'),
(9, 1009, 90.50, '2022-08-12 15:10:00'),(10, 1010, 180.00, '2022-09-30 12:30:00')

6. 家庭成员表

create table FamilyMember (
    MemberID int primary key, -- 成员ID
    MemberName varchar(50), -- 成员姓名
    Relationship varchar(50), -- 成员关系
    UserID int foreign key references userb(UserID), -- 所属用户ID 外键关联到用户表
);
insert into FamilyMember values(2, '李四', '母亲', 1002),(3, '王五', '儿子', 1003),(4, '赵六', '女儿', 1002),(5, '小明', '儿子', 1003),
(6, '小红', '女儿', 1003),(7, '刘备', '父亲', 1004),(8, '关羽', '母亲', 1004),(9, '张飞', '儿子', 1005),(10, '诸葛亮', '妻子', 1005)

7. 家庭成员设备权限表

create table MemberDevicePermission (
    PermissionID int primary key, -- 权限ID
    MemberID int foreign key references FamilyMember(MemberID), -- 成员ID 外键关联到家庭成员表
    DeviceID int foreign key references Device(DeviceID), -- 设备ID  外键关联到设备表
    HasPermission bit, -- 是否有权限
);
insert into MemberDevicePermission values(4, 2, 3, 1),(5, 3, 2, 1),(6, 4, 1, 0),
(7, 4, 4, 1),(8, 5, 3, 1),(9, 5, 5, 0),(10, 6, 2, 1)

视图设计

1. 家庭成员设备权限视图

create view vw_MemberDevicePermissionView as
select fm.MemberID, fm.MemberName, fm.Relationship, fm.PermissionID, d.DeviceName, mdp.HasPermission
from FamilyMember fm
inner join MemberDevicePermission mdp on fm.MemberID = mdp.MemberID
inner join Device d on mdp.DeviceID = d.DeviceID;

SELECT * FROM vw_MemberDevicePermissionView;

DROP VIEW vw_MemberDevicePermissionView;

总结

本文设计了一个智能家居数据库，包含传感器数据、场景管理、定时任务、消费记录、家庭成员和权限管理等功能，并提供示例数据和视图创建语句。该设计可以作为智能家居系统数据库设计的参考。