I was arguing with my colleague on how to implement a dashboard function in a web site. Suppose a user can create a dashboard, which contains multiple gadgets in the web site. We planned to add more gadgets later on, and allow users to customized more on each gadgets, such as the formula to calculate the pie chart data, if the dashboard feature is proven successful.

My question is, which approach is better in my situation in your view and why (besides the reasons I already provided)?

My colleague approach

My colleague propose to store data in the following schema.

CREATE TABLE dashboards (     id INT NOT NULL PRIMARY KEY,     name VARCHAR(50) NOT NULL );   CREATE TABLE gadgets (     id INT NOT NULL PRIMARY KEY,     parent_id INT NULL,     dashboard_id INT NULL,     category VARCHAR(10) NOT NULL,     title VARCHAR(100) NULL,     db_view_name VARCHAR(50) NULL,     -- More columns omitted... ); 

When the website has 3 type of gadgets, and users already created 2 dashboards which has 2 gadgets in each dashboard, the database will store the following information.

------------------------- | Table dashboards      | ------------------------- | id | name             | ------------------------- |  1 | Dashboard Gender | |  2 | Dashboard Score  | -------------------------  ------------------------------------------------------------------------------------------ | Table gadgets                                                                          | ------------------------------------------------------------------------------------------ | id | dashboard_id | parent_id | title             | category | db_view_name      | ... | ------------------------------------------------------------------------------------------ |  1 |       (null) |    (null) | Pie Chart Gadget  | CHART    | vw_student_gender | ... | |  2 |       (null) |    (null) | Line Chart Gadget | CHART    | vw_student_score  | ... | |  3 |       (null) |    (null) | Welcome Gadget    | HTML     | (null)            | ... | |  4 |            1 |         1 | My Pie Chart      | CHART    | vw_student_gender | ... | |  5 |            1 |         3 | My Welcome Text   | HTML     | (null)            | ... | |  6 |            2 |         3 | My Welcome Text   | HTML     | (null)            | ... | |  7 |            2 |         2 | My Line Chart     | CHART    | vw_student_score  | ... | ------------------------------------------------------------------------------------------ 

My colleague think that in this way:

1. When adding a new CHART category gadget, the developer just only need to insert a record in database, create a table view, and everything is automatically generated without any code change and web server restart, only database side change. The list of gadgets displayed to user for selection is as simple as SQL query with WHERE parent_id IS NULL.
2. Only requires update the database field to change the default value of gadget title, or any other parameters such as SQL used retrieve the data from table view.
3. Allow users to customization in very details in the future, because almost everything related to a gadget is stored in the database, we don’t need any change in database schema to support customization.

My approach

For me, I think the schema should like this:

CREATE TABLE dashboards (     id INT NOT NULL PRIMARY KEY,     name VARCHAR(50) NOT NULL );  CREATE TABLE gadgets (     id INT NOT NULL PRIMARY KEY,     dashboard_id INT NOT NULL,     title VARCHAR(100) NULL,     -- More columns omitted... ); 

When the website has 3 type of gadgets, and users already created 2 dashboard which has 2 gadgets in each dashboard, the database will store the following information.

------------------------- | Table dashboards      | ------------------------- | id | name             | ------------------------- |  1 | Dashboard Gender | |  2 | Dashboard Score  | -------------------------  --------------------------------------------- | Table gadgets                             | --------------------------------------------- | id | dashboard_id | title           | ... | --------------------------------------------- |  4 |            1 | My Pie Chart    | ... | |  5 |            1 | My Welcome Text | ... | |  6 |            2 | My Welcome Text | ... | |  7 |            2 | My Line Chart   | ... | --------------------------------------------- 

I think that information such as default gadget title, table view and SQL which is used to retrieve the source data should be put in source code. Probably in several classes with inheritance such as class Gadget, class Chart, class PieChart. In doing so:

1. We can avoid a lot NULL value, inconsistent data and duplicated data in database.
2. Track change is easier because information are stored in source code with version control.
3. The feature of the gadget can be more flexible because it is not bound by the predefined categories and database fields which used in my colleague approach.