建议:在设计典型的用户组关系数据库表时遇到的困境-Advice:dilemmaindesigningtypicaluser-group-relationDBtables

I came across this very typical user-group-relation problem in one of my recent projects. Basically, there are several users and groups in the system, and each user may join any group he likes. Currently, I design database tables as following

我在最近的一个项目中遇到了一个非常典型的用户组关系问题。基本上，系统中有几个用户和组，每个用户可以加入他喜欢的任何组。目前，我设计数据库表如下

Design 1

[user_table] id, name, email

[user_table]id、名称、电子邮件

[group_table] id, name, intro

[group_table]id、名称、介绍

[relation_table] uid, gid

[relation_table]uid、gid

When implementing "list all users of a given group" function, it involves at least two tables: relation_table and user_table. To use one db connection as much as possible, I use a JOIN-clause. But the problem is: where should I put this function, userDAO or relationDAO? Since this function involves both tables, it seems to violate "one DAO for each table" rule one way or another.

当实现“列出给定组的所有用户”函数时，它涉及至少两个表:relation_table和user_table。为了尽可能多地使用一个db连接，我使用了一个JOIN-clause。但是问题是:我应该把这个函数、userDAO或relationDAO放在哪里?由于这个函数涉及到两个表，因此它似乎违反了“每个表的一个DAO”规则。

But I have another idea

但我有另一个想法

Design 2

[user_group_table] uid, uname, uemail, gid, gname, gintro

uid, uname, uemail, gid, gname, gintro

So here's only one combined table, but I can see here that user info and group info are duplicated as more users join groups. And if some column (like gintro: group introduction) is a long text column, won't it increase db size dramatically?

这里只有一个合并的表，但是我可以看到随着更多的用户加入组，用户信息和组信息被复制。如果某个列(比如gintro: group introduction)是一个很长的文本列，它不会显著增加db的大小吗?

Moreover, if some columns are updatable, won't it be very low efficient to update a group that has thousands of users in it?

此外，如果某些列是可更新的，那么更新一个拥有数千用户的组的效率不是很低吗?

How would you guys solve this kind of problems? Really need some advice here!

你们怎么解决这种问题?这里真的需要一些建议!

A follow-up question:

一个后续的问题:

Assuming I'm using raw JDBC here and have to parse raws into objects in DAOs. So I should at least write "user rows to user objects" code once in userDAO. Now if I follow the first design, I would have to write it one more time in relationDAO since I shall have to use column alias to distinct two "name" columns. Most of these two pieces of code are identical but I have to maintain them in two places. Looks a little unwielding to me.

假设我在这里使用的是原始JDBC，并且必须在DAOs中将raws解析为对象。因此，我至少应该在userDAO中编写一次“用户行到用户对象”代码。现在，如果我遵循第一个设计，我将不得不在relationDAO中再写一次，因为我必须使用列别名来区分两个“name”列。这两段代码中的大部分是相同的，但是我必须在两个地方维护它们。看起来对我来说有点乱。

Second follow-up question:

第二个后续问题:

Now if I have a search function on user table, which uses SQL LIKE-clause on name and email together (sth. like: SELECT * FROM user_table u WHERE u.name LIKE "frog%" OR u.email LIKE "frog%"), and my "evil" P.M. asks me for another "search users from a given group" function that behaves the same as the original search function except that only users from a given group are returned. So here again, I'm facing a function involving two tables and part of its logic (the search related clause) is in userDAO. Where should I place this function?

现在，如果我在user表上有一个搜索函数，它在名称和电子邮件上使用SQL like -clause(例如:SELECT * FROM user_table u，其中的u.name如“frog%”或u)。像“frog%”这样的电子邮件)，我的“邪恶”下午请求我提供另一个“来自给定组的搜索用户”函数，该函数的行为与原始搜索函数相同，但只返回来自给定组的用户。这里，我面对的是一个包含两个表的函数，它的部分逻辑(搜索相关子句)在userDAO中。这个函数应该放在哪里?

1 个解决方案

public IEnumerable getUsersForGroup(int groupId);

public IEnumerable getGroupsForUser(int userId);