Click here to Skip to main content
15,906,097 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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.

What I have tried:

Given the requirements above the initial 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 separated by comma that the user opt to choose to view e.g. user1 has name,phone or user2 has name,address,birth-date. 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.

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.
Posted
Updated 8-Feb-18 10:48am
v2
Comments
David_Wimbley 7-Feb-18 0:33am    
So what is your quetion? I don't see a question, just you laying out your plan for implementing a schema.
Member 13664912 7-Feb-18 1:02am    
sorry, 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?
David_Wimbley 7-Feb-18 1:38am    
My bad i didn't notice the scrolling in the code box of your text.

1 solution

Well... Seems you need to create Role-based access control - Wikipedia[^]. I'd strongly recommend to implement in your own project.

Note: You have to separate presentation layer from user privileges. If you would like to provide functionality which enables user to change a view, you should do that on application level by "saving" user settings/preferences. You can store them in a file (application config) or in a database. The choice belongs to you!

For further details, please see:
Store and retrieve settings and other app data - UWP app developer | Microsoft Docs[^]
Save and restore your form size and location[^]
Where should I store my data?[^]
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900