“Excel like”编辑的数据库设计 [英] Database Design for “Excel like” editing

问题描述

我正在开发一种产品设计，允许用户编辑网格中的数据。用户交互非常类似于excel。它们具有列标题，可以添加，删除列，重新排列等。它们还可以添加，插入和删除行。 目前我最大的设计问题是尝试设计DAL，以便用户可以在类似excel的数据集中间插入行。我的理解是你不能在数据库表的中间插入行。问题是如何设计这个限制？

I’m working on a design for a product that allows users to edit data in the grid. The user interaction is very similar to that of excel. They have column headers, they can add, remove columns, re-arrange them etc. They can also add, insert, and delete rows.   My biggest design problem at the moment is trying to design the DAL such that they users can insert rows into the middle of the data set similar to excel. My understanding is that you cannot insert rows into the middle of a database table. Question is how to design around this limitation?

 

我倾向于在每个表中都有一个行位置列，当我为用户提取行时总是包含一个order rowposition子句，以便始终对用户的视图进行排序。我们希望支持包含多达一百万行数据的表。由于所有查询的顺序，这是否会变得难以置信？

I’m leaning towards having a row position column in each of the tables and when I fetch rows for the user always include an order rowposition clause so that the user’s view is always sorted. We are hoping to support tables with up to a million rows of data. Is this going to scale incredibly poorly because of the order by on all the queries?

用于插入新行我我想我会增加所有行的行位置，其行位置大于插入行的新位置。 类似于：

For inserting new rows I was thinking I’d increment the row position of all the rows with a row position greater than the new place for the inserted row.   Something like:

InsertRows（int position，int count）

InsertRows(int position, int count)

{

//更新表集rowposition = rowposition + count其中rowposition> = position

    //update table set rowposition=rowposition+count where rowposition >= position

For（int index = position; index< position + count; index ++ ）

  For(int index=position; index<position+count;index++)

{

//添加行

/ /将新行的行位置设置为索引

             //set rowposition of new row to index

}

}

使用视图会更好吗？或者甚至可以更好地将所有数据复制到一个新表中，并将行放在正确的位置，并在select语句中跳过view或order by子句？

Would I be better off using a view? Or maybe even better copying all the data to a new table with the rows in the correct place, and skipping the view or order by clause on select statements?

类似于：

//创建新表

//插入到newtable select * from existingtable其中rowposition< position

//insert into newtable select * from existingtable where rowposition <position

//插入新行

//插入到newtable select * from existingtable其中rowposition > = position

//insert into newtable   select * from existingtable where rowposition   >= position

我只是想知道是否有人在尝试探索我的选项之前解决了 类似问题。

I’m just wonder if anyone has solved  a similar problem before trying to explore my options.

谢谢

推荐答案

据我所知，订单SQL中的by子句不是世界上最快的东西。有多达一百万行，这会受到伤害。但是，您的用户是否真的会一次编辑多行？我很怀疑它，因为我不知道很多人（或应用程序）可以处理那么多数据。

如何建立某种类型的寻呼系统？假设表中有10,000行。并且"行位置"是指"行位置"。柱。您的用户想要获得第15页。你知道你的"页面"将容纳200行。简单的数学表示要获得行位置在3000和3200之间的行。然后你只需要订购200行，而且你不必担心复杂的插入。

然后，当用户获取下一页数据，你从3200 - 3400开始。

在这种情况下，你仍然做的工作与预先订购相同，可能更多，但你'在较小的块中进行，这使得应用程序看起来更快。

不确定这是否有效。

As I understand it, the order by clause in SQL is not the fastest thing in the world. With up to a million rows, that would hurt. But, will your users really be editing that many rows at a time? I rather doubt it, as I don't know many people (or apps) that coould handle that much data.

How about setting up a paging system of some sort? Say you have 10,000 rows in the table. And a "row position" column. Your user wants to get the 15th page. You know that your "pages" will hold 200 rows. Simple math says to get the rows with the row position between 3000 and 3200. Then you're only ordering on 200 rows, plus you don't have to worry about doing complex inserts.

Then, when the user gets the next page of data, you go from 3200 - 3400.

In this case, you're still doing the same amount of work as ordering by up front, maybe more, but you're doing it in smaller chunks, which makes the app seem faster.

Not sure if that would work.

这篇关于“Excel like”编辑的数据库设计的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！