1

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.
4

Storing defaults in the database is a good idea, since that allows for example a super-admin to change these things, or easier delivery of foreign-language versions.

Storing them in the same table as the concrete data elements, on the other hand, is not a good idea. It forces the dashboard_id to be nullable, which it really shouldn't be.

  • How about storing the table view name or SQL query in the database which will be used by the gadget? Any opinion on that? – VCD Jan 10 '18 at 1:15
  • That is bad, because it requires you to execute dynamic SQL, and I never think that's good. – Sebastian Redl Jan 10 '18 at 13:06
2

I'll be honest, I hate the idea of looking up something in the database to query later. But that's not the point of this question....

I would ask the following questions:

  • What risk is associated with each choice? I.e. what happens if someone removes a row in your gadgets table?
  • How likely is that risk to be realized? I.e. is there any way it can be done by accident?
  • What benefit does each approach provide?
  • Is the benefit worth the extra complexity?

In other words, you have to think in terms of risk and reward. If the extra complexity doesn't buy you much and can be broken easily, it's not a better solution.

However, if this is a data driven app that will need to update it's gadgets faster than you can release a new version, or releasing a version requires everyone to download a new copy--then you can justify the complexity and secure the solution better.

These are answers that are project specific.

  • I don't quite understand your meaning for the first sentence I'll be honest, I hate the idea of looking up something in the database to query later. But that's not the point of this question...., mind if you explain more (though probably not related to the question) ? – VCD Jan 10 '18 at 1:16
  • 1
    I have to maintain an application where you look up in a table a query or stored procedure to run, which in turn may invoke other things. It's not well contained. In the OP's demonstration table was a column to look up a view to use for the gadget. That's taking configurability a step too far in my opinion. – Berin Loritsch Jan 10 '18 at 13:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.