sql - designing a follower/following schema between two tables in SQL?
问题描述
suppose we have two tables:
- Brands
- Users
i want to design a system where:
- Brands can follow Brands
- Brands can follow Users
- Users can follow Brands
- Users can follow Users
this is what i have done so far:
- ed_id: user_id of the followed
- er_id: user_id of the follower
note: the two tables are different but i haven't written all the attributes to keep things simple
while this works fine among the Users and i can return the followers for each user with some simple queries, i don't know how to establish a relationship between Users and Brands so that they can follow each other.
any help is appreciated!
解决方案
There are a few ways to do this.
It seems that "user" and "brand" are very similar. They share many attributes - in fact, if you replace "brandname" and "username" with "name", they'd be identical.
This could lead you to a range of different designs.
I you think "actually, there are lots of differences, I've just left them out to keep the question simple", you might say that there is a superclass called User
, and two subclasses called Brand
and Person
. There are several ways to model inheritance in relational databases. You might then have a model as follows:
Users
-----
User_id (pk)
hashed_password
salt
phonenumber
address
Person
------
person_id (pk)
user_id (fk)
person_name
....
Brand
-----
brand_id (pk)
user_id (fk)
brand_name
....
Follow
-----
user_id_follower (fk)
user_id_followee (fk)
However, you might conclude that there is no meaningful semantic or behavioural difference between "person" and "brand" - that the type of user is merely an attribute of that user. In that case, life gets even easier:
Users
-----
User_id (pk)
hashed_password
salt
phonenumber
address
name
user_type ('brand', 'person')
Follow
-----
user_id_follower (fk)
user_id_followee (fk)
推荐阅读
- angular - 如何修复无法读取 Angular 中未定义的属性
- javascript - 将 Javascript 嵌入批处理文件以清除 Internet Explorer SSL 状态
- python - pandas concat给出无效的BXL错误sql server 2017
- java - 没有找到 void org.opencv.core.Core.rotate_0 的实现
- javascript - 如何最好地使用 React.Fragment
- c# - 在 .Net Core Console App 上为 DateTimeOffset 格式等设置全球文化
- java - Java不执行相同的命令,而直接在cmd.exe中运行,如何?
- javascript - 单击按钮时如何编写js函数以获取输入值
- c# - 将 JSON 字符串转换为 C# 对象
- express - 快递路线不落在'/_api/'上,而是落在'/*'上?