设计一个数据库，用于存储具有任意数量字段的表

I need to build a leads aggregating system. In general what the system would do is storing the data a client sends from a landing page (name, phone, email etc).

我需要建立一个潜在的聚合系统。通常，系统将执行的是存储客户端从登录页面（名称，电话，电子邮件等）发送的数据。

The interesting part is that I don't know ahead what fields each landing page will contain. So, one landing page might need name, phone and email, while other landing page will gather email and num_of_kids. I will also need to save the caption for the field somehow - because I need to display num_of_kids as Number of Children. I was thinking of simply storing the whole thing in one field as a JSON object, but that felt cheap and wrong (and it would suck sorting and selecting it).

有趣的是，我不知道每个着陆页将包含哪些字段。因此，一个目标网页可能需要名称，电话和电子邮件，而其他目标网页则会收集电子邮件和num_of_kids。我还需要以某种方式保存字段的标题 - 因为我需要将num_of_kids显示为Number of Children。我只想把整个东西存放在一个字段中作为JSON对象，但这感觉很便宜和错误（并且它会吮吸排序并选择它）。

I'm open to suggestions and workaround ideas (the solution doesn't have to be strictly MySQL).

我愿意接受建议和解决方法的想法（解决方案不一定是严格的MySQL）。

EDIT: I've decided to use MongoDB for that project. At the moment it looks like one of the best decisions I've made this year.

编辑：我决定将MongoDB用于该项目。目前它看起来像是我今年做出的最佳决定之一。

3 个解决方案

#1

Your problem can be solved with schema-less, NoSQL database like MongoDB

你的问题可以通过像MongoDB这样的无模式NoSQL数据库来解决

#2

#store minimal required data as the lead
CREATE TABLE leads (
   leads_id SERIAL,
   name TINYTEXT
)

#Store the rest of the data as key-value pair
CREATE TABLE leads_data (
   leads_id BIGINT UNSIGNED NOT NULL,
   field_name TINYTEXT NOT NULL,
   field_value TINYTEXT NOT NULL
);

or you can store the field names in one table, and the value in a 3th

或者您可以将字段名称存储在一个表中，并将值存储在第3个表中

CREATE TABLE leads_fields (
   leads_field_id SERIAL,
   field_name TINYTEXT NOT NULL,
   field_type TINYTEXT NOT NULL
);

CREATE TABLE leads_data (
   leads_id BIGINT UNSIGNED NOT NULL,
   leads_field_id BIGINT UNSIGNED NOT NULL,
   field_value TINYTEXT NOT NULL,
   PRIMARY KEY(leads_id, leads_field_id)
);

#3

Have you considered storing the variable form data in an XML field? Then you could use ExtractValue with XPath to get the values

您是否考虑过将变量表单数据存储在XML字段中？然后你可以使用ExtractValue和XPath来获取值

#1