locked
SQL Tables for Excel-Like table App RRS feed

  • Question

  • I am designing a database for a web app that shows a table which initially has a predetermined column names that can be of any type (text area, dropdown, date, textbox). The user also has the capability to add new columns, add new options for dropdown types, reorder the columns, hide and show column, and sort.(Excel-like behavior). The goal here is to provide the user a table in which they can input rows of data in which can be used for reporting and such. For multiple users, each user would have their own "view" (some users may opt to hide some columns) of that table.

    Given the requirements above the inital database design I come up with is:

    [This table would house all the data in a row of the table and all its predetermined fields]

    Table Name : AppRow

    Columns : Id, Name, Phone, Address, Birthdate

    [The tables below represent the new fields/column the user would add]

    TableName : CustomColumnFields

    Columns : Id, Name

    TableName : CustomColumnvalues

    Columns : Id, AppRowId, CustomColumnFieldsId, Value

    [The purpose of this table is to have the value column[string] have the column names sepertated by comma that the user opt to choose to view e.g. user1 has name,phone or user2 has name,address,birhtdate. It would basically dictate what columns should be presented to each user]

    TableName : UserView

    Columns : Id, UserId, Value

    ** Pleae note that we can assume the Database is exclusive ONLY for this set of users. They are are not sharing the DB to any other groups.

    **** For this case also, the options for the dropdown type columns will be housed on a json file.

    **** I am using SQL Server and plan to use C# for web api and AngularJS for front end

    Having the requirements above and the structure, can anyone please help me assess if there are pitfalls, violation of design principle or any red flag?

    I understand that what I am doing here is I am making the "Row" of the table as the entity of my application it is for the reason that I can easily insert data and edit data by row. This is in contrast if I design it per "Cell" or saving the values of each "Cell" on the database.

    Please share your thoughts. Thank you.

    Wednesday, February 7, 2018 5:15 AM

Answers

  • So it is just one table?

    Using this pattern you have suggested will work, but honestly, I far prefer to just use a normal table. For your use, start with a table like you have:

    Table Name : AppRow

    Columns : Id, Name, Phone, Address, Birthdate

    and two stored procedures:

    AppRow$AddColumn
    AppRow$DropColumn --Making sure that this procedure will not drop any of the base columns you have created.

    Those procedures will use simple ALTER TABLE commands, probably by using elevated security on the procedure (using WITH EXECUTE AS ...) in the declaration. If there is a lot of NULL values planned, consider using SPARSE columns. This lets them be indexed in a natural way, and you can easily implement complex datatypes and check constraints as well if you want to limit the values the column can handle (instead of it being a blob of text that the user can put anything.).

    While this adding and removing columns will seem odd at first, the final solution will then be simply a table like any other table. No pivoting/flattening/manipulating the output to fit into a simple tabular data stream.

    Then you can create a view as you suggest to show each user the view of the data that they want.

     


    Louis

    Without good requirements, my advice is only guesses. Please don't hold it against me if my answer answers my interpretation of your questions.

    Tuesday, February 13, 2018 2:11 AM

All replies

  • Doesnt look to have any issues. I've seen many apps which follows this kind of approach (EAV) where you store attributes values as rows within another table to give it more flexibility. The only issue I've seen is in cases where reporting is involved, you would sometimes experience some performance issues due to the amount of tables and joins involved + additional manipulations like pivotting to get the columns in the format you want.

    Only question I've on this is on UserView table. Why do you need to store the columns to be visible  in comma separated format like this? Cant you follow the same design as in case of custom column to store them as rows and then attach a flag to them to indicate the visibility. That way you may even be able to reuse the CustomColumnFields  table itself for this. You would just require a UserId column to link to user who has the column settings


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Wednesday, February 7, 2018 5:23 AM
  • Thank you very much for your reply! It is great help.

    The reason why I design it to be a comma separated string is because I have to consider the standard columns too to know what are viewable or not. I can do this with the CusitomColumnFields to have a flag on the table whether it is visible or not by using FK but how am I suppose to know which among the standard fields (Columns on the AppRow table) are also viewable?

    Thursday, February 8, 2018 4:22 AM
  • So it is just one table?

    Using this pattern you have suggested will work, but honestly, I far prefer to just use a normal table. For your use, start with a table like you have:

    Table Name : AppRow

    Columns : Id, Name, Phone, Address, Birthdate

    and two stored procedures:

    AppRow$AddColumn
    AppRow$DropColumn --Making sure that this procedure will not drop any of the base columns you have created.

    Those procedures will use simple ALTER TABLE commands, probably by using elevated security on the procedure (using WITH EXECUTE AS ...) in the declaration. If there is a lot of NULL values planned, consider using SPARSE columns. This lets them be indexed in a natural way, and you can easily implement complex datatypes and check constraints as well if you want to limit the values the column can handle (instead of it being a blob of text that the user can put anything.).

    While this adding and removing columns will seem odd at first, the final solution will then be simply a table like any other table. No pivoting/flattening/manipulating the output to fit into a simple tabular data stream.

    Then you can create a view as you suggest to show each user the view of the data that they want.

     


    Louis

    Without good requirements, my advice is only guesses. Please don't hold it against me if my answer answers my interpretation of your questions.

    Tuesday, February 13, 2018 2:11 AM