类似于Excel的表格应用程序的SQL表格 [英] Sql tables for excel-like table app

问题描述

我正在为一个Web应用程序设计一个数据库，该数据库显示一个表，该表最初具有预定的列名，该列名可以是任何类型(文本区域，下拉列表，日期，文本框).用户还可以添加新列，为下拉列表类型添加新选项，对列进行重新排序，隐藏和显示列以及进行排序(类似于Excel的行为).这里的目标是为用户提供一个表，他们可以在其中输入可用于报告等的数据行.对于多个用户，每个用户将对该表有自己的视图"(某些用户可能选择隐藏某些列).

我尝试过的事情:

鉴于以上我对初始数据库设计的要求，我想到的是:

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

**请注意，我们可以假定数据库仅对此组用户专有.他们没有与其他任何组共享数据库.

****对于这种情况，下拉类型列的选项也将存储在json文件中.

具有上述要求和结构，是否有人可以帮助我评估是否存在陷阱，违反设计原则或任何危险信号?

我知道我在这里所做的就是将表的行"作为应用程序的实体，这是因为我可以轻松地插入数据并按行编辑数据.相反，如果我针对每个"Cell"进行设计或将每个"Cell"的值保存在数据库中，则与此相反.

请分享您的想法.谢谢.

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

推荐答案

好...似乎您需要创建基于角色的访问控制-Wikipedia [ ^ ].我强烈建议您在自己的项目中实施.

注意:您必须将表示层与用户权限分开.如果您想提供使用户能够更改视图的功能，则应在应用程序级别通过保存"用户设置/首选项来做到这一点.您可以将它们存储在文件(应用程序配置)或数据库中.选择权属于您！

有关更多详细信息，请参见:
存储和检索设置以及其他应用程序数据-UWP应用程式开发人员| Microsoft文档 [保存并恢复您的表单大小和位置 [ ^ ]
我应该在哪里存储我的数据? [

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?[^]

这篇关于类似于Excel的表格应用程序的SQL表格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！