用户定义字段 PHP Mysql
我目前正在构建一个小型 crm 应用程序。我需要每个用户都能够定义自己的自定义字段。我目前正在使用 php 和 mysql 构建这个 crm。
示例：我有一个“客户”表，其中包含标准字段：姓名、电话、地址、电子邮件等。但我想允许用户（唯一会话）添加针对他/她的业务自定义的字段，这些字段仅他可以访问（其他用户不能访问）。然后，我希望这些自定义字段的功能与表中的所有其他字段一样（搜索、发送和接收数据的能力）。我希望我可以在 mysql 和 php 中完成此任务，但我对任何被认为是最佳实践的技术或解决方案持开放态度。感谢您的帮助。
I am currently building a small crm application. I need each user to be able to define their own custom fields. I am currently building this crm using php and mysql.
Example: I have a "customer" table which has the standard fields: name, phone, address, email, etc. But i want to allow the user (unique session) to add fields that are custom to his/her business which are only accessible to him (not other users). I then want these custom fields to function just like all the other fields in the table (ability to search, send and received data). I am hoping i can accomplish this in mysql and php but am open to any technology or solution that is considered best practice. Thank you for your help.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
这可以通过创建一个名为“customfields”的表来完成，其中包含元素“id、fieldname、company_id”，然后创建另一个将这些自定义字段与数据关联的表，例如“customercustomdata：id、customfields_id、customer_id”。以相同的方式关联字段的“所有权”
要创建新的自定义字段，“插入自定义字段 (fieldname,company_id) 值 ('生日',companyid);”
这有帮助吗？
This can be done by creating a table called "customfields" with the elements "id, fieldname, company_id", then another table that would associate those custom fields with data, eg "customercustomdata: id, customfields_id, customer_id". Associate "ownership" of a field the same way
To create a new custom field, "insert into customfields (fieldname,company_id) values ('Birthday',companyid);"
Does that help?
@Matt H：这种方法被认为是 AEV 还是只是标准关系数据库？
因此，因为我在许多不同的行业中有许多用户希望将自己的自定义字段添加到许多不同的表（联系人、交易、事件等）中，所以我假设我需要自定义字段表来拥有 user_fk/ id 或公司 fk/id、相关表 fk/id、id 和字段名称？我走在正确的轨道上吗？然后，需要创建第二个表来保存每个自定义字段的数据，购买具有自定义字段 fk/id、客户 fk/id、id 和数据字段来保存实际数据。这是正确的吗？
好的，一旦我构建了这两个额外的表，如何将它们添加到联系人表中，这样它对于用户来说看起来就像一张大表，而不是 3 个表？
再次感谢您的帮助。
答案
经过大量研究，我发现大多数希望实现此目标的人都使用文档数据库而不是关系数据库。
@Matt H: Is this method considered AEV or just standard relational db?
So because i will have many users in many dif industries that will want to add their own custom fields to a number of different tables (contacts, transactions, events, etc) i am assuming that i would need the customfield table to have a user_fk/id or company fk/id, a related table fk/id, an id, and a field name? Am i on the right track? Then in the need to create a 2nd table to hold the data for each custom field buy having a customfield fk/id, customer fk/id, id and a data field to hold the actual data. Is this correct?
Ok so once i build those two additional tables how do I add them to the contacts table so it looks like one big table for the user, instead of the 3 tables?
Thanks again for you help.
Answer
after much research i have found that most people who wish to accomplish this are using document databases not relational databases.
您可以放置一个额外的列来存储字符串数据，并存储一个描述自定义单元格内容的数组。例如：
然后使用 PHP json_encode 之类的东西将该数据存储在额外的单元格中。然后您需要做的就是解码和处理数组。
You could place an extra column for storing string data and store an array describing the contents for custom cells. For example:
then use something like PHPs json_encode to store that data in the extra cell. Then all you would need to do is decode and process the array.
有些人建议使用 Entity-Attribute-Value 设计，但在你这样做之前，请阅读 Bad CaRMa，一个关于 EAV 类的故事差点毁掉的设计一家公司，因为它无法维护。
为了更好地解决这个问题，请阅读FriendFeed 如何使用 MySQL 存储无模式数据。您可以将所有自定义列集中到一个 BLOB 中，并以这种方式存储它。然后，如果您希望单个属性可搜索，请为该属性创建一个表，将值映射回客户表。
Some people suggesting using the Entity-Attribute-Value design, but before you do, please read Bad CaRMa, a story about an EAV-like design that nearly destroyed a company because it was unmaintainable.
To solve this better, read How FriendFeed uses MySQL to store schema-less data. You can lump all the custom columns into a single BLOB, and store it that way. Then if you want individual attributes to be searchable, create a table for that attribute, that maps values back to the customers table.