5星级的数据库模式和Sql

Question

要求：

1. All "Users" have 5 fixed set of same "Services" which they are supposed to rate.
2. Given the userId, display all the Services and their corresponding "Ratings". 
3. Rating would be from a scale of 1 - 5 only
4. Ratings can be updated anytime by the user for any of his Service.

我不是SQL/DB专家，我在堆栈中提出了这个问题，我从Raj这里得到了ER Model of User Ratings的答案。

这是ER I的最后定稿(开放供更正)。

​

My Fetch查询：获取所有服务及其对应的userId (例如userId = 1)

SELECT service.service_id, service.name, usr.ratings 
FROM services service, 
     ratings ratings, 
     userservices_ratings usr,
     user user,
     user_services us
where 
    us.userid = user.uid and 
    us.serviceid = service.serviceid and
    usr.usid = us.id and
    usr.rid = ratings.rid and
    user.id = 1

1. 上述查询将返回所有服务和评级，只有当用户已经对该服务进行评级时。对于未分级的服务，不要将其设置为结果集。因此，我假设我应该预先填充未分级服务的默认值，比如0？
2. 所有用户都会一直有固定的5个服务集，每当创建用户时，我是否会编写触发器来创建他的条目到User_Services表中？
3. 如果用户if = 1，serviceId = 1的rates =3，那么create/update查询看起来如何？
4. 架构中有缺陷吗？

Answer 1

您可以在这里选择分级架构的标准格式，https://schema.org/Rating可以从中选择所需的列。