检查我的数据库设计/ PHP/MySQL

I'm currently working on improving my database to make room for growth. As it stands, different users have different 'permissions' to areas of the website. Some users have permissions to multiple areas of the website.

我目前正在改进我的数据库，为增长腾出空间。就目前的情况而言，不同的用户对网站的不同区域有不同的“权限”。有些用户可以访问网站的多个区域。

I'd like some feedback if I'm doing this in the most efficient way:

如果我以最有效的方式来做，我想要一些反馈:

 tblUsers:
    usrID       usrFirst       usrLast       phone    //etc....
      1            John          Doe 
      2            Jane          Smith
      3            Bill          Jones          


 tblAreas: 
    id      name   
     1       Marketing
     2       Support
     3       Human Resources
     4       Media Relations

 tblPermissions:

    id       usrID       areaID   
    1          1           2
    2          1           4
    3          2           1
    4          3           3

Right now, for each "area", I have separate directories. However, I'd like to minimize all of these directories down to one main directory, and then redirect users on logging in to their appropriate 'area' based upon their permissions.

现在，对于每个“区域”，我都有单独的目录。但是，我希望将所有这些目录最小化到一个主目录，然后根据它们的权限将用户重定向到其适当的“区域”。

Does it sound like I'm doing this correctly? I've never created a multi-layered site with different permissions and different groups of people, thus, I'm certainly open to learning more on how to do this correctly.

听起来我做得对吗?我从来没有创建过一个拥有不同权限和不同人群的多层网站，因此，我当然愿意学习更多如何正确地做到这一点。

Thanks very much!

非常感谢!

3 个解决方案

#1

The general design is ok. The issues that pop out on me relate to naming.

总体设计还可以。我突然想到的问题与命名有关。

SQL doesn't need hungarian notation -- generally considered unnecessary / bad (tblUsers -> users).
SQL不需要匈牙利符号——通常认为是不必要的/不好的(tblUsers ->用户)。
I wouldn't prefix table-names to column-names ...
我不会把表名加上列名……
... except for column "id" which should always include your table name (i.e. areaId)
…除了“id”列之外，“id”列应该包含您的表名(即areaId)
Your "first" and "last" column don't make sense (hint: firstName)
你的“第一”和“最后”一栏没有意义(提示:firstName)
I'd rename tblPermissions -> userAreas
我将重命名tblPermissions—>用户区域

Depending on your programming language and database, I'd also recommend using underscore instead of capitalization for your table/column-names.

根据您的编程语言和数据库的不同，我还建议对表/列名称使用下划线而不是大小写。

As for using separate directories for different groups, I'd advise against it. Have the security-checks in your code instead of your directory layout.

至于为不同的组使用不同的目录，我建议不要这么做。在代码中而不是目录布局中进行安全检查。

Reasoning:

推理:

What happens when somebody decides that support is also allowed to do some marketing stuff? Should you change your code, or add a record into your database?

当有人决定支持也被允许做一些营销工作时会发生什么?您应该更改代码，还是向数据库中添加一条记录?

Or what if you have overlapping actions?

或者如果你有重叠的动作呢?

@brianpeiris: A couple of things come to mind:

我想到了几件事:

No need for column aliases in JOINs
不需要在连接中使用列别名
Makes it easier to search through code ("foo_id" gives less results than "id")
更容易通过代码进行搜索(“foo_id”的结果比“id”要少)
JOIN USING (foo_id) instead of JOIN ON (foo.id=bar.id).
使用(foo_id)进行连接，而不是在(foo.id=bar.id)上进行连接。

#2

The schema looks fine.

模式看起来不错。

I would suggest that you put access control in the controller and base it of of URL path, so that you are not coding it into every section.

我建议您将访问控制放在控制器中，并将其作为URL路径的基础，这样就不会将其编码到每个部分。

#3

Yes, this seems like it is addressing your need perfectly from the database side.

是的，看起来它很好地满足了数据库方面的需求。

The challenge will be using the data as simply and declaratively as possible. Where is the right place to declare what "area" you are in? Does each page do this, or is there a function that calculates it, or can your controllers do it? as someone suggests. The second part is evaluating the current user against this. Ideally you end up with a single function like "security_check_for_area(4)" that does it all.

挑战将是尽可能简单和声明地使用数据。哪里是正确的地方宣布你所在的“区域”?每个页面都是这样做的吗?或者有一个函数来计算它，或者你的控制器能做到吗?作为一个建议。第二部分是对当前用户进行评估。理想情况下，您将得到一个像“security_check_for_area(4)”这样的函数，这样就可以完成所有工作。

#1