您将如何设计数据库以允许用户定义的模式 [英] How would you design your database to allow user-defined schema

问题描述

如果您必须创建一个应用程序 - 比方说一个博客应用程序，创建数据库模式相对简单。你必须创建一些表，tblPosts，tblAttachments，tblCommets，tblBlaBla ...这就是（确定，我知道，这有点简化，但你明白我的意思）。

如果您有一个应用程序，您希望允许用户在运行时在运行时定义部分模式，该怎么办？假设您想要构建一个应用程序，用户可以记录任何类型的数据。一个用户想要记录他的工作时间（startTime，endTime，项目Id，描述），下一个想要收集烹饪食谱，其他人可能股票报价，他们的婴儿每周的重量，他们花费在食物的每月支出，他们的结果最喜欢的足球队或任何你能想到的东西。

如何设计一个数据库来容纳所有非常不同类型的数据？你会创建一个通用模式，可以容纳所有类型的数据，你会创建新的表反映用户数据模式，还是你有另一个伟大的想法这样做？

如果重要的话：我必须使用SQL Server / Entity Framework

解决方案

让我们再试一次。

如果你希望他们能够创建自己的模式，那么为什么不使用构建模式，哦，我dunno，CREATE TABLE统计。你有一个完整的船，全功能，强大的数据库，可以做惊人的事情，如定义模式和存储数据。为什么不使用它？

如果你只是想做一些特别的属性，那么肯定。

但是如果它是carte blanche，他们可以做任何他们想要的，然后让他们。

他们不得不知道SQL吗？嗯，没有。这是你的UI任务。作为工具和应用程序设计师的工作是隐藏用户的实现。因此，如果你想要关系等，现在的字段，线和箭头的列表。无论如何。

多年来，人们一直在做最终用户，简单数据库工具。

如果他们想要添加列怎么办？然后添加一个列，数据库做到这一点，至少最好的。如果没有，创建新表，复制旧数据，删除旧的数据。

如果他们想要删除一个列怎么办？往上看。如果您的列无法移除列，请从用户的逻辑视图中删除，以使其看起来像已删除。

如果他们有十万亿行数据？然后，它们有数十亿行的数据和操作比如果他们有1行数据需要几十万亿倍的时间。如果他们有十亿行数据，他们可能不应该使用你的系统。

在数据库上实现数据库的迷恋不说我。 / p>

我有Oracle在这里，我如何提供更少的功能，使得用户更慢？

Gee，我不知道。

If you have to create an application like - let's say a blog application, creating the database schema is relatively simple. You have to create some tables, tblPosts, tblAttachments, tblCommets, tblBlaBla… and that's it (ok, i know, that's a bit simplified but you understand what i mean).

What if you have an application where you want to allow users to define parts of the schema at runtime. Let's say you want to build an application where users can log any kind of data. One user wants to log his working hours (startTime, endTime, project Id, description), the next wants to collect cooking recipes, others maybe stock quotes, the weekly weight of their babies, monthly expenses they spent for food, the results of their favorite football teams or whatever stuff you can think about.

How would you design a database to hold all that very very different kind of data? Would you create a generic schema that can hold all kind of data, would you create new tables reflecting the user data schema or do you have another great idea to do that?

If it's important: I have to use SQL Server / Entity Framework

解决方案

Let's try again.

If you want them to be able to create their own schema, then why not build the schema using, oh, I dunno, the CREATE TABLE statment. You have a full boat, full functional, powerful database that can do amazing things like define schemas and store data. Why not use it?

If you were just going to do some ad-hoc properties, then sure.

But if it's "carte blanche, they can do whatever they want", then let them.

Do they have to know SQL? Umm, no. That's your UIs task. Your job as a tool and application designer is to hide the implementation from the user. So present lists of fields, lines and arrows if you want relationships, etc. Whatever.

Folks have been making "end user", "simple" database tools for years.

"What if they want to add a column?" Then add a column, databases do that, most good ones at least. If not, create the new table, copy the old data, drop the old one.

"What if they want to delete a column?" See above. If yours can't remove columns, then remove it from the logical view of the user so it looks like it's deleted.

"What if they have eleventy zillion rows of data?" Then they have a eleventy zillion rows of data and operations take eleventy zillion times longer than if they had 1 row of data. If they have eleventy zillion rows of data, they probably shouldn't be using your system for this anyway.

The fascination of "Implementing databases on databases" eludes me.

"I have Oracle here, how can I offer less features and make is slower for the user??"

Gee, I wonder.

这篇关于您将如何设计数据库以允许用户定义的模式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！