I want to save permissions for both individual users, and user groups. In my mysql database, I have a permissions table where I store a permission name, and a permission id. I have a user table, where I store a username, password etc. which also contains an id, and I have a groups table which stores a group name and group id.
我想保存单个用户和用户组的权限。在我的mysql数据库中,我有一个权限表,我在其中存储权限名称和权限ID。我有一个用户表,我存储了一个用户名,密码等,其中还包含一个id,我有一个组表,用于存储组名和组ID。
What would now be the most efficient option? To make 2 tables, one containing user permissions and one containing group permissions, so something like this:
什么是现在最有效的选择?要制作2个表,一个包含用户权限,另一个包含组权限,如下所示:
int group id | int permission id
int user id | int permission id
or would it be better to have one table like this;
或者这样有一张桌子会更好;
int id | int permission id | enum('user','group')
3 个解决方案
#1
1
I doubt there would be much of a performance difference between your two approaches; but, as with all performance and optimization questions, feelings and guesses don't matter, only profiled results matter. Which one will be more efficient depends on your data, your database, your access patterns, and what "efficient" means (space? time? developer effort? final monetary cost?) in your context.
我怀疑你的两种方法之间会有很大的性能差异;但是,与所有性能和优化问题一样,感觉和猜测无关紧要,只有异形结果才有意义。哪一个更有效取决于您的数据,数据库,访问模式以及您的上下文中“有效”意味着什么(空间?时间?开发人员的努力?最终的货币成本?)。
That said, using two tables is a better structure as it allows you to have foreign keys from your group-permission table back to your group table and your user-permission table back to your user table. Even if it was faster in one table I'd still go with two: data integrity is more important than wasting a couple µs of processor time, I don't see much point in quickly accessing unreliable or broken data.
也就是说,使用两个表是一个更好的结构,因为它允许您将组权限表中的外键返回到组表,将用户权限表中的外键返回到用户表。即使它在一个表中更快,我仍然会选择两个:数据完整性比浪费几μs的处理器时间更重要,我没有看到快速访问不可靠或损坏数据的重点。
#2
2
I would recommend using two tables.
我建议使用两张桌子。
First you can reduce the seeking time by checking the first table for group permission and if there is no group permission to search for user permission.
首先,您可以通过检查第一个表的组权限以及是否没有组权限来搜索用户权限来缩短搜索时间。
Second it will be easier to understand by other programmers.
其次,其他程序员更容易理解。
#3
0
The first way looks more time-efficient, the second way looks more space-efficient. For the speed of a unique index, in the 2nd case you'd have to index on the 1st and 3rd fields.
第一种方式看起来更节省时间,第二种方式看起来更节省空间。对于唯一索引的速度,在第二种情况下,您必须在第1和第3个字段上编制索引。
Mulling over it a little more, any potential gains of doing it the 2nd way aren't worth it, IMO. There may be some third way, but of the two you posted the first is superior. Simple, clean, and fast.
#1
1
I doubt there would be much of a performance difference between your two approaches; but, as with all performance and optimization questions, feelings and guesses don't matter, only profiled results matter. Which one will be more efficient depends on your data, your database, your access patterns, and what "efficient" means (space? time? developer effort? final monetary cost?) in your context.
我怀疑你的两种方法之间会有很大的性能差异;但是,与所有性能和优化问题一样,感觉和猜测无关紧要,只有异形结果才有意义。哪一个更有效取决于您的数据,数据库,访问模式以及您的上下文中“有效”意味着什么(空间?时间?开发人员的努力?最终的货币成本?)。
That said, using two tables is a better structure as it allows you to have foreign keys from your group-permission table back to your group table and your user-permission table back to your user table. Even if it was faster in one table I'd still go with two: data integrity is more important than wasting a couple µs of processor time, I don't see much point in quickly accessing unreliable or broken data.
也就是说,使用两个表是一个更好的结构,因为它允许您将组权限表中的外键返回到组表,将用户权限表中的外键返回到用户表。即使它在一个表中更快,我仍然会选择两个:数据完整性比浪费几μs的处理器时间更重要,我没有看到快速访问不可靠或损坏数据的重点。
#2
2
I would recommend using two tables.
我建议使用两张桌子。
First you can reduce the seeking time by checking the first table for group permission and if there is no group permission to search for user permission.
首先,您可以通过检查第一个表的组权限以及是否没有组权限来搜索用户权限来缩短搜索时间。
Second it will be easier to understand by other programmers.
其次,其他程序员更容易理解。
#3
0
The first way looks more time-efficient, the second way looks more space-efficient. For the speed of a unique index, in the 2nd case you'd have to index on the 1st and 3rd fields.
第一种方式看起来更节省时间,第二种方式看起来更节省空间。对于唯一索引的速度,在第二种情况下,您必须在第1和第3个字段上编制索引。
Mulling over it a little more, any potential gains of doing it the 2nd way aren't worth it, IMO. There may be some third way, but of the two you posted the first is superior. Simple, clean, and fast.