小部件驱动的站点的数据库架构建议

I am currently working on restructuring my site's database. As the schema I have now is not one of the best, I thought it would be useful to hear some suggestions from you.

我目前正在重建我的网站的数据库。由于我现在的模式不是最好的模式之一,我认为听取您的一些建议会很有用。

To start off, my site actually consists of widgets. For each widget I need a table for settings (where each instance of the widget has its user defined settings), a table for common (shared items between instances of the same widget) and userdata (users' saved data within an instance of a widget).

首先,我的网站实际上包含小部件。对于每个窗口小部件,我需要一个用于设置的表(窗口小部件的每个实例都有其用户定义的设置),一个用于公共的表(同一窗口小部件的实例之间的共享项)和用户数据(用户在窗口小部件实例中保存的数据) )。

Until now, I had the following schema, consisting of 2 databases:

到目前为止,我有以下架构,由2个数据库组成:

the first database, where I had all site-maintenance tables (e.g. users, widgets installed, logs, notifications, messages etc.) PLUS a table where I joined each widget instance to each user that instanciated it, having assigned a unique ID (so, I have the following columns: user_id, widget_id and unique_id).

第一个数据库,我有所有网站维护表(例如用户,安装了小部件,日志,通知,消息等)。加上一个表,我将每个小部件实例加入到实例化的每个用户,并分配了一个唯一的ID(所以,我有以下列:user_id,widget_id和unique_id)。

the second database, where I kept all widget-related data. That means, for each widget (unique by its widget_id) I had three tables: [widget_id]_settings, [widget_id]_common and [widget_id]_userdata. In each of these tables, each row held that unique_id of the users' widget. Actually here was all the users' data stored within a widget.

第二个数据库,我保存了所有与widget相关的数据。这意味着,对于每个小部件(由widget_id唯一)我有三个表:[widget_id] _settings,[widget_id] _common和[widget_id] _userdata。在每个表中,每行都保存用户窗口小部件的unique_id。实际上,这是存储在窗口小部件中的所有用户数据。

To give a short example of how my databases worked:

举一个我的数据库如何工作的简短例子:

First database:

In the users table I have user_id = 1

在users表中,我有user_id = 1

In the widgets table I have widget_id = 1

在小部件表中,我有widget_id = 1

In the users_widgets table I have user_id = 1, widget_id = 1, unique_id = 1

在users_widgets表中,我有user_id = 1,widget_id = 1,unique_id = 1

Second database:

In the 1_settings I have unique_id = 1, ..., where ... represents the user's widget settings

在1_settings中,我有unique_id = 1,...,其中...表示用户的小部件设置

In the 1_common I have several rows which represent shared data between instances of the same widget (so, no user specific data here)

在1_common中,我有几行代表同一小部件的实例之间的共享数据(因此,这里没有用户特定的数据)

In the 1_userdata I have unique_id = 1, ..., where ... represents the user's widget data. An important notice here is that this table may contain several rows with the same unique_id (e.g. For a tasks widget, a user can have several tasks for a widget instance)

在1_userdata中,我有unique_id = 1,...,其中...表示用户的小部件数据。这里一个重要的通知是该表可能包含多个具有相同unique_id的行(例如,对于任务窗口小部件,用户可以为窗口小部件实例执行多个任务)

Hope you understood in the rough my database schema.

希望你能粗略理解我的数据库架构。

Now, I want to develop a 'cleaner' schema, so it won't be necessary to have 2 databases and switch each time from one to another in my application. It would be also great if I found a way NOT to dinamically generate tables in the second database (1_settings, 2_settings, ... , n_settings).

现在,我想开发一个“更清洁”的模式,因此没有必要在我的应用程序中拥有2个数据库并且每次都从一个数据库切换到另一个数据库。如果我找到一种不在第二个数据库(1_settings,2_settings,...,n_settings)中动态生成表格的方法也会很棒。

I will greatly appreciate any effort in suggesting any better way of achieving this. Thank you very much in advance!

我将非常感谢任何建议任何更好的方法来实现这一目标。非常感谢你提前!

EDIT: Shall I have databases like MongoDB or CouchDB in my mind when restructurating my databases? I mean, for the second database, where it would be better if I didn't have a fixed schema. Also, how would traditional SQL's and NoSQL's get along on the same site?

编辑:重构我的数据库时,我的脑海中是否有像MongoDB或CouchDB这样的数据库?我的意思是,对于第二个数据库,如果我没有固定的架构会更好。此外,传统SQL和NoSQL如何在同一网站上相处?

2 个解决方案

#1

A possible schema for the users_widgets table could be:

users_widgets表的可能模式可以是:

id | user_id | widget_id

You don't need the unique_id field in the users_widgets table, unless you want to hide the primary key for some reason. In fact, I would rename this table to something a little more memorable like widget_instances, and use widget_instance_id in the remaining tables of the second database.

除非您出于某种原因要隐藏主键,否则您不需要users_widgets表中的unique_id字段。实际上,我会将此表重命名为像widget_instances更令人难忘的内容,并在第二个数据库的其余表中使用widget_instance_id。

One way to handle the second set of tables is by using a metadata style:

处理第二组表的一种方法是使用元数据样式:

widget_instance_settings

id | widget_instance_id | key | value

This would include the userdata, because user_id is related to the widget_instance_id, unless you want to allow a user to create multiple instances of the same widget, and have the same data across all instances for some reason.

这将包括userdata,因为user_id与widget_instance_id相关,除非您希望允许用户创建同一窗口小部件的多个实例,并且由于某种原因在所有实例中具有相同的数据。

widget_common_settings

id | widget_id | key | value

This type of schema can be seen in packages like Elgg.

这种类型的架构可以在像Elgg这样的包中看到。

#2

Do you know the settings a widget class and widget instance could have? In this case these settings could be made columns of the widget_class table (for common settings) and widget_instance (for instance specific settings).
If you don't know them, then you could have a widget_class_settings table that has a many to one relation with the widget_class table and a widget_instance_settings that has a many to one relation to the widget_instance table. Between the widget_instance and the widget_class you could, again, have a many to one relation. The widget_instance could also have a foreign key in the users table, so that you know which user created a specific widget.

您知道窗口小部件类和窗口小部件实例可能具有的设置吗?在这种情况下,这些设置可以是widget_class表(用于常见设置)和widget_instance(例如特定设置)的列。如果您不了解它们,那么您可以拥有一个与widget_class表具有多对一关系的widget_class_settings表,以及与widget_instance表具有多对一关系的widget_instance_settings。在widget_instance和widget_class之间,您可以再次拥有多对一关系。 widget_instance还可以在users表中具有外键,以便您知道哪个用户创建了特定的窗口小部件。

#1