如何设计一个通用数据库，其布局可能随时间而变化？ [英] How to design a generic database whose layout may change over time?

问题描述

这里有一个棘手的问题 - 我如何以编程方式创建和查询一个数据库，其内容我不能真正预见。

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

因此，我们有三个阶段：

1. 一个表单被设计和生成。这是一次性过程，虽然可以稍后编辑表单。这个设计数据库。


2. 有人或几个人使用这种形式 - 例如日常销售报告，库存，工资单等。他们对表单的输入被写入数据库。


3. 其他人，也许是管理人员，可以查询数据库并生成报告。

是通用的，我不能预测数据库结构 - 除了说它将反映HTML表单字段，并包括从编辑框，备忘录，单选按钮等集合的数据输入。

问题与注解：

A）我如何才能最好地根据表和列来构造数据库？主键怎么样？我的第一个想法是使用控件名称来标识每个列，然后我意识到用户可以编辑窗体和重命名，以便可能名称变为雇员或工资变为：工资。我倾向于每个的唯一编号。

B）如何最好地键行？我正在想一个时间戳，以允许我查询和列的行Id从A）

C）我必须处理列重命名/插入/删除。 Foe删除，我不确定是否从数据库中删除数据。即使用户不再从表单输入它，他也可能希望查询先前输入的内容。或者可能有一些法律要求来保留数据。列重命名/插入/删除中的任何问题？

D）对于查询，我可以让我的PHP查询数据库以获取列名称并生成一个列表其中每个条目都有一个数据库列名，一个复选框，说明是否应该在查询中使用，并且基于列类型，一些选择条件。这应该足以建立搜索，如职位='高级销售员和工资> 50k。

E）我可能需要生成一些花哨的图表 - 直方图，饼图等，用于随时间查询数值数据的结果。我需要为此找到一些好的FOSS PHP。

F）还忘了什么？

对我来说似乎很棘手，但我是数据库n00b - 也许这是很简单的大师​​？

---

不要告诉我不要这样做。我没有任何选择： - （

编辑：在现实生活中，我不认为列重命名/插入/删除是频繁的，在运行几个月后，可能需要对数据库进行更改，我相信这种情况经常发生，我担心我已经严重地说了这个问题，人们认为，每10分钟左右就会进行修改。 / p>

实际上，我的用户在定义数据库时会定义一个数据库，他们可能会第一次获得正确的数据，并且永远不会改变它 - 尤其是如果他们从纸质表单转换。即使他们决定改变，这可能只会发生一次或两次，之后几个月或几年 - 这可能发生在任何数据库。

我不认为我在这里有一个特殊的情况，也不应该专注于改变，也许更好的集中在链接 - 什么是一个好的主键方案？或许，对于一个文本输入，一个数字和一个备忘录？

解决方案

这对我来说似乎很棘手，但
我是数据库n00b - 也许它是
简单到你的大师？

不，它真的是棘手的。从根本上说，你所描述的不是数据库应用程序，它是一个数据库应用程序 builder 。事实上，它听起来好像你想编码的东西像 Google App Engine 或网络版的MS访问。编写这样的工具需要花费大量的时间和专业知识。

Google通过使用BigTable平台实现了灵活的模式。它允许您几乎随意地灵活扩展模式。抓住的是，这种灵活性使得很难写如position ='senior salesman'and salary> 50k的查询。

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

元数据架构需要存储的内容

对于架构生成：

• 外键关系（EMPLOYEE在DEPARTMENT中工作）


• 唯一的业务键（只能有一个DEPARTMENT称为Sales


• 参考资料（EMPLOYEE.POSITION的允许值）


• 栏数据类型，大小等


• 是否为可选（即NULL或NOT NULL）


• 复杂的业务规则（员工奖金不能超过其工资的15％）


• 列

适用于前端世代

• 显示名称或标签（工资，工资）


• 小部件（下拉列表，弹出日历）


• / li>
  
• 派生字段


• 帮助文字，提示


• 客户端验证li>
  

最后一点是你的建议的潜在的复杂性：一个常规的表单设计师，如Joe Soap不能够制定JS （说）验证一个输入值是在X和Y之间，所以你必须使用模板规则导出。

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

对于主键，我建议您使用GUID数据类型的列。时间戳不保证是唯一的，虽然如果你在一个操作系统上运行你的数据库到六个地方（即不是Windows），你不会发生冲突。

最后一个字

'我的第一个想法是使用
控制名来识别每一列，
然后我意识到用户可以编辑
的形式和重命名，所以也许
name变成employee或工资
变为：salary。 '

我之前已经构建了数据库模式生成器。他们很难。一个可能很困难的事情是调试动态SQL。所以让自己更容易：为表和列使用真实名称。只是因为应用程序用户现在想要查看名为HEADCOUNT的表单，这并不意味着您必须重命名EMPLOYEES表。因此，需要将显示的标签与模式对象名称分开。否则你会发现自己试图找出为什么这个生成的SQL语句失败：

  update table_11123 
 set col_55542 = 'HERRING'
其中col_55569 ='Bootle'
 / 
  

谎言。

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.

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. 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.
3. others, maybe management, can query the database and generate reports.

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.

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.

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)

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?

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".

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.

F) What else have I forgotten?

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

---

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.

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?

解决方案

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

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.

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".

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.

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")
• reference data (permitted values of EMPLOYEE.POSITION)
• column data type, size, etc
• whether column is optional (i.e NULL or NOT NULL)
• complex business rules (employee bonuses cannot exceed 15% of their salary)
• 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)

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.

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 clashes.

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.'

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:

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

That way madness lies.

这篇关于如何设计一个通用数据库，其布局可能随时间而变化？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！