基于用户首选项的动态表列 [英] Dynamic table columns based on user preferences
问题描述
假设用户是推销员。用户模型有许多log_entries用作销售数据的每日日志。用户还具有允许他们选择在log_entry表单中可见的字段的首选项。因此，如果他们选择菠萝，香蕉和葡萄...这些就是表格中的字段。如果这些选择更改，数据将不会丢失...只是在窗体中不可见。
现在，可以说用户刚刚获得了蔓越莓销售帐户，但蔓越莓在其用户首选项形式中并不是可选择的属性。他们需要能够创建该类别。现在，菠萝季节结束了，所以他们登录那里的偏好设置，然后取消选中菠萝框和草莓框。现在，当他们在日志中输入数据时，将有一个草莓数据字段，而不是菠萝数据字段。菠萝数据没有消失，只是没有显示。
Lets say a user is a salesman. The User model has many log_entries used as a daily log for sales data. The user also has preferences that allow them to select what fields are visible in their log_entry form. So, if they select pineapples, bananas, and grapes...those are the fields that will be in the form. If these choices change, the data will not be lost...just not visible in the form. Now, lets say the user just picked up cranberry sales account, but cranberries are not a choosable attribute in their user preferences form. They need to be able to create that "category". Now, pineapple season is over so they log in to there preferences and uncheck the pineapple box and check the strawberries box. Now, when they go to enter data in their log there will be a data field for strawberries, but not pineapples. The pineapple data is not gone...its just not being shown.
这种情况带来了一些挑战。一种是作为开发人员，我不提前知道数据库列的名称，因为用户可以即时创建新的类别（我可能会要求用户与我联系以根据需要添加更多内容，但是...）。而且，当用户创建一个新类别时，log_entries表将没有一列代表该新数据。
This scenario creates a couple challenges. One being that as a developer I don't know the database column names ahead of time because the users can create new 'categories' on the fly (I could require that users contact me to add more as needed, but...). And, when a user creates a new category...the log_entries table wont have a column to represent that new data.
如何管理动态数据库列？ postgres hstore可以处理吗？
How can I manage dynamic db columns? Can postgres hstore handle this?
根据日期，一行是属于用户的条目。每列包含特定于属性的数据（例如...一列标题为草莓，它将保存当天售出的商品数量）
One row is an entry belonging_to a user based on date. Each column holds data specific to an attribute (such as...one column could be titled 'strawberries' and it would hold how many units were sold that day)
推荐答案
通常的解决方法是：
- EAV
- hstore
- XML
- JSON
- EAV
- hstore
- XML
- JSON
请参阅：
- Database design - should I use 30 columns or 1 column with all data in form of JSON/XML?
- https://dba.stackexchange.com/q/27057/7788
整个使列可供其他用户使用只是要求您保留一个自定义键表，只要用户定义了一个先前未使用的键，便会添加到该表中。
The whole "make columns available to other users" thing just requires you to keep a "custom keys" table that you add to whenever a user defines a previously unused key.
用d添加列最初，动态DDL听起来很合理，但是可以存储多少列以及行的宽度是有限的。当您添加更多列时，扫描表的性能会变差，尽管大多数为空的稀疏列相对便宜。添加列需要排他锁，这可能会花费一些时间才能进入繁忙的系统，尽管如果未将其定义为 NOT NULL DEFAULT，则添加列的自身非常快... 。刚开始会很好用，但是我怀疑您以后会后悔的。
Adding columns with dynamic DDL sounds reasonable at first, but there are limits to how many columns you can store and how "wide" a row can be. Performance of scanning the table gets worse as you add more columns, though "sparse" columns that are mostly null are relatively cheap. An exclusive lock is required to add a column, something that can take time to get on a busy system, though adding the column its self is very quick if it isn't defined as NOT NULL DEFAULT .... It'll work fairly well at first, but I suspect you will regret doing it later.
这篇关于基于用户首选项的动态表列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
