如何设计一个布局可能随时间变化的通用数据库?

Here's a tricky one - how do I programatically create and interrogate a database whose contents I can't really foresee?

这里有一个棘手的问题——我如何程序化地创建和查询一个我无法真正预见其内容的数据库?

I am implementing a generic input form system. The user can create PHP forms with a WYSIWYG layout and use them for any purpose he wishes. He can also query the input.

我正在实现一个通用的输入表单系统。用户可以创建带有WYSIWYG布局的PHP表单，并将其用于任何他希望的目的。他还可以查询输入。

So, we have three stages:

我们有三个阶段

1. a form is designed and generated. This is a one-off procedure, although the form can be edited later. This designs the database.
2. 设计并生成表单。这是一个一次性的过程，尽管稍后可以编辑表单。本设计数据库。
3. someone or several people make use of the form - say for daily sales reports, stock keeping, payroll, etc. Their input to the forms is written to the database.
4. 一些人或几个人使用这个表格——比如日常销售报告、存货记录、工资表等等。他们对表格的输入是写在数据库上的。
5. others, maybe management, can query the database and generate reports.
6. 其他的，可能是管理，可以查询数据库并生成报告。

Since these forms are generic, I can't predict the database structure - other than to say that it will reflect HTML form fields and consist of a the data input from collection of edit boxes, memos, radio buttons and the like.

由于这些表单是通用的，所以我无法预测数据库结构——除了说它将反映HTML表单字段，并包含来自编辑框、备忘录、单选按钮等集合的数据输入。

Questions and remarks:

问题和评论:

A) how can I best structure the database, in terms of tables and columns? What about primary keys? My first thought was to use the control name to identify each column, then I realized that the user can edit the form and rename, so that maybe "name" becomes "employee" or "wages" becomes ":salary". I am leaning towards a unique number for each.

A)如何根据表和列对数据库进行最佳的结构?主键是什么?我的第一个想法是使用控件名来标识每个列，然后我意识到用户可以编辑表单并重命名，所以可能“name”变成“employee”或者“salary”变成“salary”。我倾向于给每个人一个唯一的数字。

B) how best to key the rows? I was thinking of a timestamp to allow me to query and a column for the row Id from A)

B)如何最好地键排?我想要一个允许查询的时间戳和a中的行Id的列)

C) I have to handle column rename/insert/delete. Foe deletion, I am unsure whether to delete the data from the database. Even if the user is not inputting it from the form any more he may wish to query what was previously entered. Or there may be some legal requirements to retain the data. Any gotchas in column rename/insert/delete?

C)我必须处理列重命名/插入/删除。由于Foe删除，我不确定是否要从数据库中删除数据。即使用户不再从表单中输入它，他也可能希望查询先前输入的内容。或者可能存在保留数据的法律要求。列rename/insert/delete有什么问题吗?

D) For the querying, I can have my PHP interrogate the database to get column names and generate a form with a list where each entry has a database column name, a checkbox to say if it should be used in the query and, based on column type, some selection criteria. That ought to be enough to build searches like "position = 'senior salesman' and salary > 50k".

D)查询,我可以有我的PHP询问数据库列名并生成一个表单列表,每个条目都有一个数据库列名,应该使用复选框说如果查询中,基于列类型,一些选择标准。这应该足以构建“职位=‘高级销售人员’和‘工资>50k’这样的搜索。”

E) I probably have to generate some fancy charts - graphs, histograms, pie charts, etc for query results of numerical data over time. I need to find some good FOSS PHP for this.

E)我可能需要生成一些花哨的图表——图表、直方图、饼图等等，以便查询数字数据的结果。我需要找到一些好的FOSS PHP。

F) What else have I forgotten?

F)我还忘了什么?

This all seems very tricky to me, but I am database n00b - maybe it is simple to you gurus?

这一切对我来说似乎都很棘手，但我是数据库n00b——也许对你们大师来说很简单?

---

Edit: please don't tell me not to do it. I don't have any choice :-(

编辑:请不要告诉我不要这么做。我别无选择:

Edit: in real life I don't expect column rename/insert/delete to be frequent. However it is possible that after running for a few months a change to the database might be required. I am sure this happens regularly. I fear that I have worded this question badly and that people think that changes will be made willy-nilly every 10 minutes or so.

编辑:在现实生活中，我不希望列重命名/插入/删除频繁。但是，在运行几个月之后，可能需要对数据库进行更改。我确信这种情况经常发生。我担心我把这个问题说得很糟，人们会认为每10分钟左右就会做出改变——不管愿不愿意。

Realistically, my users will define a database when they lay out the form. They might get it right first time and never change it - especially if they are converting from paper forms. Even if they do decide to change, this might only happen once or twice ever, after months or years - and that can happen in any database.

实际上，我的用户在布局表单时将定义一个数据库。他们可能第一次就做对了，而且从来没有改变过——尤其是当他们从纸上转换的时候。即使他们决定改变，这种情况在几个月或几年后也只会发生一到两次，而且在任何数据库中都可能发生。

I don't think that I have a special case here, nor that we should be concentrating on change. Perhaps better to concentrate on linkage - what's a good primary key scheme? Say, perhaps, for one text input, one numerical and a memo?

我不认为我有什么特别的理由，也不认为我们应该专注于改变。或许最好专注于链接——什么是好的主键方案?比如说，一个文本输入，一个数字和一个备忘录?

8 个解决方案

#1

"This all seems very tricky to me, but I am database n00b - maybe it is simple to you gurus?"

“这一切对我来说似乎都很棘手，但我是数据库n00b——也许对你们大师来说很简单?”

Nope, it really is tricky. Fundamentally what you're describing is not a database application, it is a database application builder. In fact, it sounds as if you want to code something like Google App Engine or a web version of MS Access. Writing such a tool will take a lot of time and expertise.

不，这真的很棘手。基本上，您所描述的不是一个数据库应用程序，而是一个数据库应用程序构建器。实际上，这听起来就像是你想要编码谷歌应用程序引擎或web版本的MS Access。编写这样的工具需要花费大量的时间和专业知识。

Google has implemented flexible schemas by using its BigTable platform. It allows you to flex the schema pretty much at will. The catch is, this flexibility makes it very hard to write queries like "position = 'senior salesman' and salary > 50k".

谷歌使用其BigTable平台实现了灵活的模式。它允许您随意伸缩模式。问题是，这种灵活性使得编写诸如“position = 'senior salesman'和salary > 50k”之类的查询变得非常困难。

So I don't think the NoSQL approach is what you need. You want to build an application which generates and maintains RDBMS schemas. This means you need to design a metadata repository from which you can generate dynamic SQL to build and change the users' schemas and also generate the front end.

所以我不认为NoSQL方法是您需要的。您希望构建一个能够生成和维护RDBMS模式的应用程序。这意味着您需要设计一个元数据存储库，您可以从中生成动态SQL来构建和更改用户的模式，并生成前端。

Things your metadata schema needs to store

For schema generation:

模式生成:

• foreign key relationships (an EMPLOYEE works in a DEPARTMENT)
• 外键关系(员工在部门工作)
• unique business keys (there can be only one DEPARTMENT called "Sales")
• 唯一的业务密钥(只能有一个部门称为“Sales”)
• reference data (permitted values of EMPLOYEE.POSITION)
• 参考数据(雇员的允许值。职位)
• column data type, size, etc
• 列数据类型、大小等。
• whether column is optional (i.e NULL or NOT NULL)
• 列是否可选(i)。e零或非零)
• complex business rules (employee bonuses cannot exceed 15% of their salary)
• 复杂的业务规则(员工奖金不能超过其工资的15%)
• default value for columns
• 默认值为列

For front-end generation

前端代

• display names or labels ("Wages", "Salary")
• 显示姓名或标签(“工资”、“工资”)
• widget (drop down list, pop-up calendar)
• 小部件(下拉列表，弹出日历)
• hidden fields
• 隐藏字段
• derived fields
• 导出字段
• help text, tips
• 帮助文本,建议
• client-side validation (associated JavaScript, etc)
• 客户端验证(相关的JavaScript等)

That last points to the potential complexity in your proposal: a regular form designer like Joe Soap is not going to be able to formulate the JS to (say) validate that an input value is between X and Y, so you're going to have to derive it using templated rules.

最后这一点指出了您的提议中潜在的复杂性:像Joe Soap这样的常规表单设计器将无法构造JS来(比如)验证输入值在X和Y之间，因此您将不得不使用模板化规则派生它。

These are by no means exhaustive lists, it's just off the top of my head.

这些绝不是详尽的列表，它只是在我的头顶上。

For primary keys I suggest you use a column of GUID datatype. Timestamps aren't guaranteed to be unique, although if you run your database on an OS which goes to six places (i.e. not Windows) it's unlikely you'll get *es.

对于主键，我建议您使用GUID数据类型列。时间戳不能保证是唯一的，但是如果你在一个操作系统上运行你的数据库，它会有6个地方(也就是说不是Windows)，你不太可能会遇到冲突。

last word

'My first thought was to use the control name to identify each column, then I realized that the user can edit the form and rename, so that maybe "name" becomes "employee" or "wages" becomes ":salary". I am leaning towards a unique number for each.'

“我的第一个想法是使用控件名来标识每个列，然后我意识到用户可以编辑表单并重命名，这样“name”就可能变成“employee”或“salary”变成“salary”。我倾向于给每个人一个唯一的数字。

I have built database schema generators before. They are hard going. One thing which can be tough is debugging the dynamic SQL. So make it easier on yourself: use real names for tables and columns. Just because the app user now wants to see a form titled HEADCOUNT it doesn't mean you have to rename the EMPLOYEES table. Hence the need to separate the displayed label from the schema object name. Otherwise you'll find yourself trying to figure out why this generated SQL statement failed:

我以前构建过数据库模式生成器。他们是很难的。调试动态SQL是一件困难的事情。因此，让自己更容易:对表和列使用真实的名称。仅仅因为应用程序用户现在想看到一种名为HEADCOUNT的表单，并不意味着你必须重命名员工表。因此需要将显示的标签与模式对象名分开。否则，您会发现自己试图弄明白为什么生成的SQL语句失败了:

update table_11123
set col_55542 = 'HERRING'
where col_55569 = 'Bootle'
/

That way madness lies.

那种疯狂。

#2

In essence, you are asking how to build an application without specifications. Relational databases were not designed so that you can do this effectively. The common approach to this problem is an Entity-Attribute-Value design and for the type of system in which you want to use it, the odds of failure are nearly 100%.

本质上，您是在问如何构建一个没有规范的应用程序。关系数据库的设计并不是为了使您能够有效地做到这一点。这个问题的常见方法是实体-属性-值设计，对于您希望使用它的系统类型，失败的几率几乎是100%。

It makes no sense for example, that the column called "Name" could become "Salary". How would a report where you want the total salary work if the salary values could have "Fred", "Bob", 100K, 1000, "a lot"? Databases were not designed to let anyone put anything anywhere. Successful database schemas require structure which means effort with respect to specifications on what needs to be stored and why.

例如，名为“Name”的列可以变成“Salary”，这是毫无意义的。如果你希望工资总额能有“Fred”，“Bob”，100K, 1000，“很多”，你想要的工资总额是多少?数据库的设计并不是为了让任何人在任何地方放置任何东西。成功的数据库模式需要结构，这意味着关于需要存储什么以及为什么要存储的规范的工作。

Therefore, to answer your question, I would rethink the problem. The entire approach of trying to make an app that can store anything in the universe is not a recipe for success.

因此，为了回答你的问题，我将重新思考这个问题。尝试开发一款能够存储宇宙中任何东西的应用程序的整个方法并不是成功的秘诀。

#3

Like Thomas said, rational database is not good at your problem. However, you may want to take a look at NoSQL dbs like MongoDB.

正如Thomas所说，rational database并不适合您的问题。但是，您可能想看看像MongoDB这样的NoSQL dbs。

#4

See this article: http://www.simple-talk.com/opinion/opinion-pieces/bad-carma/ for someone else's experience of your problem.

看看这篇文章:http://www.simple-talk.com/opinion/opinion/opinion/bad carma/为别人体验你的问题。

#5

This is for A) & B), and is not something I have done but thought it was an interesting idea that Reddit put to use, see this link (look at Lesson 3):

这是A)和B)，这不是我做过的，但我认为这是Reddit使用的一个有趣的想法，看看这个链接(看第3课):

http://highscalability.com/blog/2010/5/17/7-lessons-learned-while-building-reddit-to-270-million-page.html

——http://highscalability.com/blog/2010/5/17/7——教训-时-建筑- reddit - - 2.7亿page.html

#6

Not sure about the database but for charts instead of using PHP for the charts, I recommend looking into using javascript (http://www.reynoldsftw.com/2009/02/6-jquery-chart-plugins-reviewed/). Advantages to this are some of the processing is offloaded to the client side for chart displays and they can be interactive.

我不太清楚这个数据库，但是对于图表，我建议使用javascript (http://www.reynoldsftw.com/2009/02/6-jquery-chart-plugins-reviewed/)而不是PHP。这样做的好处是，一些处理被转移到客户端进行图表显示，它们可以是交互式的。

#7

The other respondents are correct that you should be very cautious with this approach because it is more complex and less performant than the traditional relational model - but I've done this type of thing to accommodate departmental differences at work, and it worked fine for the amount of use it got.

其他受访者是正确的,你应该非常谨慎使用此方法,因为它是更复杂和更少的性能比传统的关系模型,但我做了这种类型的适应部门的差异在工作中,和它工作得很好使用它了。

Basically I set it up like this, first - a table to store some information about the Form the user wants to create (obviously, adjust as you need):

基本上我是这样设置的，首先——一个表来存储用户想要创建的表单的一些信息(显然，根据需要进行调整):

--************************************************************************
-- Create the User_forms table
--************************************************************************
create table User_forms
    (
    form_id            integer identity,
    name               varchar(200),
    status             varchar(1),
    author             varchar(50),
    last_modifiedby    varchar(50),
    create_date        datetime,
    modified_date      datetime
    )

Then a table to define the fields to be presented on the form including any limits and the order and page they are to be presented (my app presented the fields as a multi-page wizard type of flow).

然后有一个表来定义要在表单上显示的字段，包括任何限制、它们将要显示的顺序和页面(我的应用程序将字段显示为多页向导类型的流)。

-

- - - - - -

-************************************************************************
-- Create the field configuration table to hold the entry field configuration
--************************************************************************
create table field_configuration
    (
    field_id                integer identity,
    form_id                 SMALLINT,
    status                  varchar(1),
    fieldgroup              varchar(20),
    fieldpage               integer,
    fieldseq                integer,
    fieldname               varchar(40),
    fieldwidth              integer,
    description             varchar(50),
    minlength               integer,
    maxlength               integer,
    maxval                  varchar(13),
    minval                  varchar(13),
    valid_varchars             varchar(20),
    empty_ok                varchar(1),
    all_caps                varchar(1),
    value_list              varchar(200),
    ddl_queryfile           varchar(100),
    allownewentry           varchar(1),
    query_params            varchar(50),
    value_default           varchar(20)
    );

Then my perl code would loop through the fields in order for page 1 and put them on the "wizard form" ... and the "next" button would present the page 2 fields in order etc.

然后，我的perl代码将循环遍历字段，以获取第1页，并将它们放在“向导表单”上……“下一步”按钮将按顺序显示第2页的字段等。

I had javascript functions to enforce the limits specified for each field as well ...

我有javascript函数来执行为每个字段指定的限制……

Then a table to hold the values entered by the users:

然后，保存用户输入的值的表:

--************************************************************************
-- Field to contain the values
--************************************************************************
create table form_field_values
    (
    session_Id        integer identity,
    form_id           integer,
    field_id          integer,
    value             varchar(MAX)
    );

That would be a good starting point for what you want to do, but keep an eye on performance as it can really slow down any reports if they add 1000 custom fields. :-)

这将是您想要做的事情的一个很好的起点，但是请关注性能，因为如果它们添加了1000个自定义字段，那么它确实会减慢任何报告的速度。:-)

#8

I agree with Mark, using others experience can prevent many unforeseen mistakes especially for " database n00b ", see this article from a guy working at a company that specializing in generic back end

我同意Mark的观点，使用其他人的经验可以避免许多不可预见的错误，特别是“数据库n00b”，请参阅本文作者在一家专门研究通用后端的公司工作的一名员工