Database schema suggestion for widget driven site?

A possible schema for the users_widgets table could be.

A possible schema for the users_widgets table could be: 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. 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.

Widget_common_settings id | widget_id | key | value This type of schema can be seen in packages like Elgg.

Another great suggestion from a great user! :) Your suggestions looks quite 'solid' and scalable. This way, I probably won't need the second database as I will integrate the 3 tables in my first database.My question is that, if I have 1000 widget instances, for example, each widget with 5 different settings - this means I will have in my widget_instance_settings 5000 rows.

And this increases with every widget instance a user creates. Will MySQL be able to deal with such a large amount of data within one table? – linkyndy Nov 28 '10 at 20:09 And also, using the schema you mentioned, will it be easy for Cake (as I'm developing this project on this framework) to handle a specific widget instance consisting of 'n' rows within a database table?

(where 'n' is the number of settings a widget can have). And I'm planning to share widgets (that is, copy all data from a widget instance of one user, to the instance of another user). Will this be easy to achieve?

Thank you very much for your help. – linkyndy Nov 28 '10 at 20:12 Definitely. Just make sure you INDEX the widget_instance_id column, and maybe even the key column.

There shouldn't be any problems using this schema with CakePHP. – RabidFire Nov 28 '10 at 20:13 Thanks. Also, shall I have NoSQL in my mind for storing user data?

I mean, it could be helpful storing data in dynamic schemas. Are other pros and cons that could make me opt or not for NoSQL? – linkyndy Nov 28 '10 at 18:40 You could use NoSQL for the settings tables.

But I can't say I have enough experience with NoSQL yet to give you a clear picture. – RabidFire Nov 28 '10 at 20:56.

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.

You mixed up a little bit the tables, perhaps I wasn't very clear in my explanations. A user can CREATE a widget (and upload it on my site) and a user can USE a widget. These are two different stories.

I don't have problems with the first one, only with the second one. When a new widget is installed, 3 different tables are created in the second database (this is what happens now). Each of the 3 tables has a predefined schema, as for what the widget needs (while the tasks widget needs a task_name column, the notes widget needs a note_content column).

So all column are predefined. – linkyndy Nov 23 '10 at 17:56 Now, when a user starts using a widget, we add a new row in the users_widgets table within the first database, containing a unique_id. Then, we add a new row in widget_id_settings in the second database, using that unique_id.

The settings row is mandatory; each widget instance must be registered in the settings table. When a user adds something (e.g. A task), we create in the widget_id_userdata a row, also using the unique_id, with the task. The thing I wanted to know is how can I improve this system, thus not having two databases,from which one consisting of 'n' tables.

Thanks! – linkyndy Nov 23 '10 at 18:03.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.