PGSQL学习 #1 简单设计一个instagram app

【自用】咱学习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：即可