将标志存储在数据库中
在我的应用程序中，我希望用户选择他的工作日。然后将它们存储在数据库中。当然，我的应用程序将处理用户的数据，例如：今天是特定用户的工作日吗？今天应该工作的用户是谁，...等等。
我的问题是，执行此操作的最佳实践是什么？我应该使用：
- 用户表中的位掩码字段
- 通过为days、users 和days_users 创建表来创建多对多关系表。 先感谢您。
In my application, I'd like the user to select his working days. then store them in the database. Of course my application will process the users' data like: Is today a working day for a specific user, who are the users that should work today , ... etc.
My question is, what is the best practice for doing this? should I use:
- Bitmasking field in the users table
- Many to many relationships tables by creating table for days, users and days_users.
Thank you in advance.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
我想说位掩码字段是一种关系反模式。
字段应该有一个有意义的值，否则您最终会遇到查询问题 - 每次需要使用该字段进行查询时都需要解析该字段。
这样的字段还需要额外的文档，因为它存储的值不是自我描述的。
I would say that bit mask fields are a relational anti-pattern.
A field should have a single meaningful value, otherwise you end up with querying issues - parsing the field every time you need to query using it.
Such a field also requires extra documentation, as the values it stores are not self describing.
位掩码字段本质上有点神秘，您需要创建其他内容来解释您在位掩码中存储的内容。
第二种方法更加透明且易于理解，如果您需要添加更多值，它也会更加灵活。使用位掩码，每次添加值时都需要重做位图解码器，与关系方法相比，这可能是维护的噩梦。
Bitmasking field is a bit more cryptic in nature and you need to create something else to interpret what you store in the bitmask.
The second approach is a lot more transparent and easily understandable and it's a bit more flexible if you need to add more values. With the bitmask, you again need to redo your bitmap decoder each time you add a value which can be a maintainance nightmare compared to the relational approach.
我犯了选择方案 1 的错误，如果有机会回到过去，我绝对会采取另一种方式。
您的数据库几乎肯定不会使用索引对您的位掩码进行按位查询。因此，如果您想找到周二工作的每个人，您将每次都进行索引扫描。当你的表变得很大时，这可能会破坏你的性能。您可以尝试通过提前缓存 SELECT DISTINCT(bitmaskfield) 来优化此问题，在您自己的应用程序中执行位掩码逻辑，并将其转换为适当的 WHERE 位掩码字段 IN (.. .) 子句，但这很快就会变得难以维护，因为您必须在更改数据库中的值的每个位置更新不同的位掩码缓存。
额外的表和连接可能看起来很痛苦，但位掩码会变得更糟。相信我。使用您的数据库作为数据库。
I have made the mistake of going with option 1, and given the opportunity to go back in time I would absolutely do it the other way.
Your database almost certainly will not use an index to make bitwise queries on your bitmask. So if you want to find, say, everyone working Tuesdays, you're going to do an index scan every time. As your tables get large, this can destroy your performance. You can try to optimize around this by caching a
SELECT DISTINCT(bitmaskfield)ahead of time, doing the bitmask logic in your own application, and turing it into an appropriateWHERE bitmaskfield IN (...)clause, but that quickly becomes unmaintainable as you then have to update your distinct-bitmask cache in every place that you change values in the database.The extra tables and joins may seem like a pain, but the bitmask will turn out worse. Trust me on this. Use your database as a database.