如何组织数据库中的相关表

发布于 2024-11-28 09:58:48 字数 568 浏览 1 评论 0原文

我有以下情况：

table User (id, name)
table Belonging (id, name, color)
table UserBelonging (id, user_id, belonging_id)

现在，UserBelonging 是我的连接表，连接 User 和他的 Belonging。

当我想取出某个用户的所有红色物品时，问题就出现了。

我看到有两种方法可以做到这一点：

获取某个用户的所有 UserBelonging ，然后循环遍历它们并检查哪些是红色的
放置一个额外的列，如下所示： UserBelonging (id, user_id, Outstanding_id, color)，然后在同一个表中查询。

这两种方法对我来说都很难看。有没有在数据库表方面有经验的人可以告诉我哪种方法更好以及为什么或者可能给我一个更好的解决方案？

谢谢

原文

I have the following situation:

table User (id, name)
table Belonging (id, name, color)
table UserBelonging (id, user_id, belonging_id)

Now, UserBelonging is my join table that connects User and his Belonging.

The problem arises when I want to pull out all belongings of a certain User, that are red.

I see two ways to do that:

Get all UserBelonging for a certain user, then loop through them and check which are red
Put an extra column like that: UserBelonging (id, user_id, belonging_id, color) and then query in the same table.

Both methods seem ugly to me. Is there anyone with experience in database tables that could tell me which way is better and why or possibly give me a better solution?

thanks

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

你穿错了嫁妆 2024-12-05 09:58:48

连接表应仅包含 user_id 和 belonging_id，复合 ID 为 (user_id, OFenging_id) 或自动增量 id 列。

这就排除了#2。

现在，您可以使用策略#1，或者可以在 select 语句中过滤掉非红色内容。

SELECT b.id, b.name, b.color
FROM UserBelonging ub
LEFT JOIN Belonging b
ON ub.belonging_id = b.id
WHERE ub.user_id = 123456789
AND b.color = 'red'

The join table should only contain user_id and belonging_id, with either a composite ID of (user_id, belonging_id) or an autoincrement id column.

That rules out #2.

Now, you could use strategy #1, or you could filter out non-red belongings in the select statement.

SELECT b.id, b.name, b.color
FROM UserBelonging ub
LEFT JOIN Belonging b
ON ub.belonging_id = b.id
WHERE ub.user_id = 123456789
AND b.color = 'red'

回复收藏 0 原文

铜锣湾横着走 2024-12-05 09:58:48

SELECT B.* FROM User U
INNER JOIN UserBelonging UB on UB.UserId=U.Id
INNER JOIN Belonging B on B.Id = UB.BelongingId
Where B.Color = 'red' AND U.Name='Sam'

我认为像这样的加入将满足您的要求。

SELECT B.* FROM User U
INNER JOIN UserBelonging UB on UB.UserId=U.Id
INNER JOIN Belonging B on B.Id = UB.BelongingId
Where B.Color = 'red' AND U.Name='Sam'

I think a join like this will do what you want.

回复收藏 0 原文

南渊 2024-12-05 09:58:48

您可以轻松地将 1 JOIN 和 A WHERE 语句与您的颜色条件和 user_id 结合使用。在讨论关系数据库时，连接是最常见的操作之一。
检查 W3schools 了解一些基本示例。

回复收藏 0 原文

寄与心 2024-12-05 09:58:48

通常最好不要在数据库中使用循环。在 SQL Server 中，我会这样做（但我不知道它是否适用于您的数据库）

    Select User.Name, Belonging.name, Belonging.color from User inner join UserBelonging 
    on User.id =     UserBelonging.user_id inner join Belonging 
    on UserBelonging.belonging_id = Belonging.id 
    where Belonging.color = 'red' and User.name = 'put the name here'

问候

蒂姆

It's generally best not to use loops in databases. In SQL Server I would do it like this (but I don't know if it's applicable to your DB)

    Select User.Name, Belonging.name, Belonging.color from User inner join UserBelonging 
    on User.id =     UserBelonging.user_id inner join Belonging 
    on UserBelonging.belonging_id = Belonging.id 
    where Belonging.color = 'red' and User.name = 'put the name here'

regards

Tim

回复收藏 0 原文

~没有更多了~