小部件驱动站点的数据库架构建议
我目前正在努力重建我的网站的数据库。由于我现在拥有的模式不是最好的模式之一，我认为听到您的一些建议会很有用。
首先，我的网站实际上由小部件组成。对于每个小部件，我需要一个设置表（其中每个小部件实例都有其用户定义的设置），一个通用表（同一小部件实例之间的共享项目） ）和userdata（用户在小部件实例中保存的数据）。
到目前为止，我有以下架构，由 2 个数据库组成：
- 第一个数据库，其中包含所有站点维护表（例如用户、安装的小部件、日志、通知、消息等）以及加入每个小部件实例的表给实例化它的每个用户分配一个唯一的 ID（因此，我有以下列：
user_id、widget_id和unique_id）。 - 第二个数据库，我在其中保存所有与小部件相关的数据。这意味着，对于每个小部件（其
widget_id是唯一的），我有三个表：[widget_id]_settings、[widget_id]_common和>[widget_id]_userdata。在每个表中，每一行都保存用户小部件的unique_id。实际上，这里是存储在小部件中的所有用户数据。
举一个简短的例子来说明我的数据库如何工作：
第一个数据库：
- 在
users表中，我有user_id = 1 - 在
widgets表中，我有widget_id = 1 - 在
users_widgets表中，我有user_id = 1, widget_id = 1, unique_id = 1
第二个数据库：
- 在
1_settings 中我有unique_id = 1, ...，其中 ... 代表用户的小部件设置 - 在
1_common中，我有几行代表实例之间的共享数据相同小部件的（因此，此处没有用户特定数据） - 在
1_userdata中，我有unique_id = 1, ...，其中 ... 代表用户的小部件数据。这里的一个重要注意事项是，该表可能包含具有相同unique_id的多行（例如，对于任务小部件，用户可以为小部件实例执行多个任务）
希望您大致了解我的数据库架构。
现在，我想开发一种“更干净”的模式，因此不需要有 2 个数据库，也不需要每次在我的应用程序中从一个数据库切换到另一个数据库。如果我找到一种不在第二个数据库中动态生成表的方法（1_settings、2_settings、...、n_settings），那就太好了。
我将非常感谢任何建议实现这一目标的更好方法的努力。预先非常感谢您！
编辑： 在重构我的数据库时，我的脑海中应该考虑像 MongoDB 还是 CouchDB 这样的数据库吗？我的意思是，对于第二个数据库，如果我没有固定的模式会更好。 另外，传统 SQL 和 NoSQL 如何在同一站点上相处？
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:
- 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_idandunique_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]_commonand[widget_id]_userdata. In each of these tables, each row held thatunique_idof the users' widget. Actually here was all the users' data stored within a widget.
To give a short example of how my databases worked:
First database:
- In the
userstable I haveuser_id = 1 - In the
widgetstable I havewidget_id = 1 - In the
users_widgetstable I haveuser_id = 1, widget_id = 1, unique_id = 1
Second database:
- In the
1_settingsI haveunique_id = 1, ..., where ... represents the user's widget settings - In the
1_commonI have several rows which represent shared data between instances of the same widget (so, no user specific data here) - In the
1_userdataI haveunique_id = 1, ..., where ... represents the user's widget data. An important notice here is that this table may contain several rows with the sameunique_id(e.g. For a tasks widget, a user can have several tasks for a widget instance)
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).
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?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
users_widgets 表的可能架构如下：
您不需要
users_widgets表中的unique_id字段，除非您出于某种原因想要隐藏主键。事实上，我会将此表重命名为更容易记住的名称，例如widget_instances，并在第二个数据库的其余表中使用widget_instance_id。处理第二组表的一种方法是使用元数据样式：
widget_instance_settings
这将包括用户数据，因为 user_id 与 widget_instance_id 相关，除非您想允许用户创建同一小部件的多个实例，并且由于某种原因在所有实例中具有相同的数据。
widget_common_settings
这种类型的架构可以在 Elgg 等包中看到。
A possible schema for the users_widgets table could be:
You don't need the
unique_idfield in theusers_widgetstable, unless you want to hide the primary key for some reason. In fact, I would rename this table to something a little more memorable likewidget_instances, and usewidget_instance_idin the remaining tables of the second database.One way to handle the second set of tables is by using a metadata style:
widget_instance_settings
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.
widget_common_settings
This type of schema can be seen in packages like Elgg.
您知道小部件类和小部件实例可以具有的设置吗？在这种情况下，这些设置可以是 widget_class 表（对于通用设置）和 widget_instance （例如特定设置）的列。
如果您不知道它们，那么您可能有一个与 widget_class 表具有多对一关系的 widget_class_settings 表，以及与 widget_instance 表具有多对一关系的 widget_instance_settings 表。在 widget_instance 和 widget_class 之间，您可以再次建立多对一的关系。 widget_instance 还可以在用户表中具有外键，以便您知道哪个用户创建了特定的小部件。
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.