在数据库中指定用户权限的最有效方式是什么？ [英] What is the most efficient way to specify user permissions in a database?

问题描述

例如：

用户A与组1和组2相关联

用户B与组2和组4相关联

用户可以为他们所属的每个组创建帖子（少量文字）。

我有两种类型的页面：登录用户的主页和每个组的页面。用户的主页显示登录用户所属组织中的每个其他用户的帖子。该组页面显示该组中每个用户的所有帖子。

我不想在此复制FaceBook，但是我可以想到的功能最接近的是，您的FaceBook上的一个朋友的消息如何显示在您的直播Feed页面上以及用户墙上（个人资料页面）。

在我的应用程序中，我有这三个模型（伪代码）：

  class User（）：
 user_name 
 first_name 
 last_name 
 
 class Group（）：
 group_name 
 
 class Post（）：
 post_content 
  

在数据库可扩展性和性能方面将这些数据关联起来最有效的方法是什么？ / strong>

1）将每个帖子关联到用户和组。当用户查看组时，从组ID =当前组的 Post 表中选择所有帖子。当用户查看自己的主页时，查看用户所属的组，并查找属于该组的所有其他用户。然后，拉出这些用户的所有帖子。

2）将所有帖子与用户关联。当用户查看自己的主页时，查看用户所属的组，并查找属于该组的所有其他用户。然后，拉这些用户的所有帖子。查看组的页面时，查找属于该组的所有用户，然后拉出与这些用户相关联的所有帖子。

3）创建一个具有PostID，UserID和GroupID。查看组时，查找所有具有GroupID的帖子并拉出这些帖子。当查看登录用户的主页时，查找用户所属的所有组，然后查找属于这些组的所有用户，然后拉取所有用户的所有帖子。

所有这些解决方案似乎需要大量额外的工作才能为用户的主页提取记录。这些选项都不是最好的解决方案。我相信有人有这方面的经验。我希望大量的帖子，所以我希望数据库可扩展，以支持X个记录数。

有没有办法做到这一点更有意义？大公司如何设法做同样的事情并尽量减少开销？

解决方案

你没有说明你有哪些dbms。

使用内置安全（权限）系统的ISO / IEC / ANSI SQL数据库，您不需要这些，只需使用SQL功能设置权限即可。它是内部的，非常快。

如果你想要性能，可扩展性和数据完整性，你需要放弃对象和类的想法，并了解一些关系数据库。此外，如果您将数据建模为关系数据库（而不是对象类），则上述90％的问题将不存在。

如果您不明白我在说什么，阅读这个 最近的帖子 （从12月10日的标题开始）。

有没有办法这更有意义大公司如何设法做同样的事情并尽量减少开销？

是的，绝对。它没有任何意义。有意义的是正确建模数据，因为标准化模型总体上要快得多。这导致更多的更小的表，而不是更少的脂肪表;算术和物理学规律。没有数据重复意味着没有更新异常，这意味着事务更小，更不可能阻止（您想要允许多个用户并发访问，对吗？）

此外，您的编码将受到阻碍，您可能无法确定数据与其他数据的关系。在您的问题中投掷您的实体（所有页面的数据内容）（编辑它并添加到其中），我们可以走了。我可以看到用户，组，发布，但是约墙;任何其他家具; 照片，相册。

I have an application that associates users with specific groups in a Facebook-style manner.

For example:

User A is associated with Group 1 and Group 2

User B is associated with Group 2 and Group 4

Users can create posts (small amounts of text) for each group that they belong to.

I have two types of pages: the home page for the logged-in user, and a page for each group. The user's home page shows posts from every other user that is in every group that the logged-in user belongs to. The group page shows all posts from every user that is in that group.

I'm not trying to replicate FaceBook here, but the closest parallel I can think of in functionality is how messages from one of your friends on FaceBook appear on your live feed page and also on the user's wall (profile page).

In my application, I have these three models (pseudocode):

class User():
    user_name
    first_name
    last_name

class Group():
    group_name

class Post():
    post_content

What is the most efficient way to associate these data in terms of database scalability and performance?

1) Associate each post to a user and a group. When a user views a group, select all posts from the Post table where Group ID = current group. When a user views their own home page, see what groups the user belongs to and find all other users that belong to that group. Then, pull all posts from those users.

2) Associate all posts with a user. When a user views their own home page, see what groups the user belongs to and find all other users that belong to that group. Then, pull all posts from those users. When viewing a group's page, find all users that belong to that group and then pull all posts associated with those users.

3) Create a join table that has PostID, UserID, and GroupID. When viewing a group, find all posts that have GroupID and pull those posts. When viewing the logged-in user's home page, find all groups the user belongs to and then find all users that belong to those groups and then pull all posts for those users.

All these solutions seem to require a lot of extra work to pull records for the user's home page. None of these options seem like the best solution. I'm sure someone has experience with this. I expect a large volume of posts, so I want the database to be scalable to support X number of records.

Is there a way to do this that makes more sense? How do the big companies manage to do the same thing and minimize their overhead?

解决方案

You have not stated which dbms you have.

With an ISO/IEC/ANSI SQL database, which has a security (permissions) system built in, you need none of that, just set permissions using the SQL facility. It is internal and very fast.

If you want performance, scalability, and data integrity, you need to give up the idea for objects and classes, and learn a little about Relational databases. Further to that, if you model your data as a Relational database (not as object classes), 90% of your questions above will not exist.

If you do not understand what I am saying, read this recent post (starting from the 11 Dec 10 heading).

Is there a way to do this that makes more sense? How do the big companies manage to do the same thing and minimize their overhead?

Yes, absolutely. It does not make sense. What makes sense is to model the data properly, because a normalised model is much faster overall. This results in more, smaller tables, rather than fewer fat tables; the arithmetic and the laws of physics work. No data duplication means no Update Anomalies, which means transactions are smaller and less likely to block (you do want to allow multiple user concurrent access, right ?).

Besides, your coding will be hindered, beacuse you can't figure out how the data relates to other data. Throw your entities (the data contents of all your pages) up in your question (edit it and add to it), and we can have a go. I can see User,Group,Post, but what about Wall; any other furniture; Photos,Albums.

这篇关于在数据库中指定用户权限的最有效方式是什么？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！