【自用】咱学习pgsql,顺便发发博客,大家可以康康,点个赞+关注吧 ୧⍢⃝୨
简单设计一个instagram,包含tables的各类功能所需的约束细节(以及插入的数据),之后笔者会发布用网页设计和各类交互(hmtl+css+js
目标功能模块:
1. 可视化效果如下:
2. 实现的table(功能):
1. users(用户):
create table users (
id serial primary key,
-- current_timestamp是常量,是sql系统读取的计算机时间
-- 每个用户都有创建和更新(资料)时间
created_at timestamp with time zone default current_timestamp,
updated_at timestamp with time zone default current_timestamp,
username varchar(30) not null,
-- bio指的是user的生平传记
bio varchar(400),
-- user的头像图片,以url存储
avatar varchar(200),
phone varchar(25),
email varchar(40),
password varchar(50),
-- user状态,类似于qq的"学习中""离线"等
status varchar(15),
-- 保证phone和email(登录时)至少得有一个
check(coalesce(phone, email) is not null)
);
2. posts(帖子):
create table posts (
id serial primary key,
-- post应该有创建和更新时间
created_at timestamp with time zone default current_timestamp,
updated_at timestamp with time zone default current_timestamp,
-- post创建所含图片,以url存储
url varchar(200) not null,
-- post创建所含内容
caption varchar(240),
-- post创建所在地点(开启定位),real为6位小数,最适合作为经纬度的数据类型
lat real check(lat is null or (lat >= -90 and lat <= 90)),
lng real check(lng is null or (lng >= -180 and lng <= 180)),
-- on delete cascade:删除约束,如果post所依赖的user没了,post也会被删除
user_id integer not null references users(id) on delete cascade
);
3. comments (评论):
create table comments (
id serial primary key,
-- 评论应该有创建和更新时间
created_at timestamp with time zone default current_timestamp,
updated_at timestamp with time zone default current_timestamp,
-- 评论的文本内容(不能为空)
contents varchar(240) not null,
-- 评论满足关系user在post上发commnet,所以要设置删除约束
user_id integer not null references users(id) on delete cascade,
post_id integer not null references posts(id) on delete cascade
);
4. likes (点赞):
create table likes (
id serial primary key,
created_at timestamp with time zone default current_timestamp,
user_id integer not null references users(id) on delete cascade,
-- 替换多态关联设计:
post_id integer references posts(id) on delete cascade,
comment_id integer references comments(id) on delete cascade,
check (
coalesce((post_id)::boolean::integer, 0)
+
coalesce((comment_id)::boolean::integer, 0)
= 1
),
-- 多列唯一性约束:
-- 防止有完全重复的like,因为不可能存在三个id相同的like
unique(user_id, post_id, comment_id)
);
多态关联设计(不推荐):
但这样就不能为liked_id创建外键列了
而对于like_id这偏统计的列是最需要保持数据一致性的,否则容易丢失或易被修改like_id
这就最好要外键列,外键列是实现数据一致性的最好方式
所以这种方式的唯一缺点便是这个
代替多态关联的方法(推荐):
添加如下约束:
COALESCE关键字的使用:
select coalesce(null, 5);
第一个值如果是null, 返回第二个值;
第一个值如果不是null, 返回第一个值;
而图中(post_id实际设置为null或者其他integer)
SELECT COALESCE((4)::BOOLEAN::INTEGER, 0)
如上返回1
因为4转换为boolean为true(非零为1,即真),真转换为integer为1,即coalesce(1, 0)返回1
而图中(post_id为null或者其他integer)
SELECT COALESCE((null)::BOOLEAN::INTEGER, 0)
(null)::BOOLEAN::INTEGER照样返回null,这里只是因为括号里的值是变量user_id,故不能直接写(null, 0), 最终返回0
而:
Add CHECK of
(
COALESCE((post_id)::BOOLEAN::INTEGER, 0)
+
COALESCE((comment id)::BOOLEAN::INTEGER, 0)
) = 1
意思是两个约束同时,而非"多列约束",这样就保证了
解析:
两null 时即 0 + 0: (0 + 0) = 0 ≠ 1, 不通过验证返回error
一null一integer时即 0 + 1: (0 + 1) = 1 = 1,通过验证,insert成功
两integer时即 1 + 1: (1 + 1) = 2 ≠ 1,不通过验证返回error
5. photo_tags (照片上的标签):
就是在创建post时所包含的照片,可以添加例如:的人名标签
create table photo_tags (
id serial primary key,
created_at timestamp with time zone default current_timestamp,
updated_at timestamp with time zone default current_timestamp,
user_id integer not null references users(id) on delete cascade,
post_id integer not null references posts(id) on delete cascade,
-- 以如下x,y坐标的方式记录tag在照片上的位置(单位可能是像素px,等等等)
x integer not null,
y integer not null,
unique (user_id, post_id)
);
6. caption_tags (文本里的标签):
可以在caption中标记user(可以添加''@user'这样更显眼的功能,或者可以点击tag进入user主页等等未来可期的功能)
create table caption_tags (
id serial primary key,
-- 你只有删除或增加tag,故没有updated_at
created_at timestamp with time zone default current_timestamp,
user_id integer not null references users(id) on delete cascade,
post_id integer not null references posts(id) on delete cascade,
--保证没有重复的photo_tag(不能@一个user多次)
unique (user_id, post_id)
);
7. hashtags(主题标签):
create table hashtags (
id serial primary key,
-- 这只用于存储固定的几个主题标签
created_at timestamp with time zone default current_timestamp,
-- 主题标签名
title varchar(20) not null unique
);
hashtag:指主题标签,类似于"#频道#主题"这样的,而之前的tag指的是"@user"这样的:
实际情况中,可能的一个设计如下:
(蓝色框指能如上图一样通过hashtag搜索posts)
(橙色框指不具备如上图一样通过hashtag搜索posts的功能 )
故既然comments,users都不需要这样的查询功能,就也没必要创建hashtags_comments和hashtags_users了,只需要hashtags——posts了。
8. hashtags_posts
create table hashtags_posts (
id serial primary key,
hashtag_id integer not null references hashtags(id) on delete cascade,
post_id integer not null references posts(id) on delete cascade,
unique(hashtag_id, post_id)
);
9. followers
create table followers (
id serial primary key,
created_at timestamp with time zone default current_timestamp,
leader_id integer not null references users(id) on delete cascade,
follower_id integer not null references users(id) on delete cascade,
unique(leader_id, follower_id)
);
followers system:
设计一个followers-table:
leader_id代表被执行关注动作的人,即如up主,follower_id代表执行关注动作的人,即如粉丝;
同时添加验证:不能关注自己(上面的验证)
也可以添加只能关注一次的验证(多列unique)
3. insert数据(参考)
这里我们使用已有的数据,一定一定要改为把txt改为.sql格式(蓝奏不让传sql
lanzouyun:1.1 ig .txt - 蓝奏云
如何使用?:
接下来右击Instagram数据库-Restore:
切换为all fire:
选择sql数据文件,选择后如下:
接下来设置一些设置,一定要设置
然后restore:即可