I want to store the spreadsheet data in database, so right now i am using key/value pattern to store the same but its taking too much time to retrieve it from data base, if the data is huge. Do anyone has the best way to do the same. How Google saves their spreadsheet data?
我想将电子表格数据存储在数据库中,所以现在我使用键/值模式来存储它,但是如果数据量很大,则需要花费太多时间从数据库中检索它。有没有人有最好的方法来做同样的事情。 Google如何保存电子表格数据?
Usually I have to show 30 to 40 columns and 10,000 rows on the web page with page size 500 or 1000 for pagination. Data is for line item with pricing.
通常我必须在网页上显示30到40列和10,000行,页面大小为500或1000以进行分页。数据适用于包含定价的订单项。
My current database structure is as below.
我目前的数据库结构如下。
Column Table Column_Id int Column_Name nvarchar(50)
Column_Type nvarchar(50)
列表Column_Id int Column_Name nvarchar(50)Column_Type nvarchar(50)
Value Content Table criteria_id int column_id int row_id int column_contents nvarchar(100)
值内容表criteria_id int column_id int row_id int column_contents nvarchar(100)
3 个解决方案
#1
A naive schema for storing a spreadsheet:
用于存储电子表格的简单模式:
create table spreadsheet
(
id INTEGER primary key,
name TEXT UNIQUE not null
);
create table cell
(
id INTEGER primary key,
spreadsheet_id INTEGER NOT NULL REFERENCES spreadsheet(id),
row INTEGER not null,
col INTEGER not null,
content TEXT NOT NULL
);
#2
If you're just loading and saving it, not querying it much, I would recommend an XML field for the entire sheet, or each page, or at least each row.
如果您只是加载并保存它,而不是查询它,我会建议整个工作表,每个页面或至少每一行的XML字段。
My other question is, how are you querying this to populate your sheet? Are you using loops and opening separate queries for each row or column?
我的另一个问题是,您如何查询填充表格?您是否正在使用循环并为每行或每列打开单独的查询?
Your design may be suboptimal, but it actually should be performing decently for the data set you're describing, IMHO.
你的设计可能不是最理想的,但它实际上应该适合你所描述的数据集,恕我直言。
#3
Considering that Excel is widely considered to be the default tool for a database, I'm not sure there's anything about the kinds of data that is stored in spreadsheets except it's a single table and there's a limit on the number of rows.
考虑到Excel被广泛认为是数据库的默认工具,我不确定存储在电子表格中的数据种类是什么,除了它是单个表并且行数有限制。
I don't think there's enough distinction to say anything else than "It's the data that determines the structure, not the source."
我不认为除了“这是确定结构而非来源的数据”之外,还有其他的区别。
#1
A naive schema for storing a spreadsheet:
用于存储电子表格的简单模式:
create table spreadsheet
(
id INTEGER primary key,
name TEXT UNIQUE not null
);
create table cell
(
id INTEGER primary key,
spreadsheet_id INTEGER NOT NULL REFERENCES spreadsheet(id),
row INTEGER not null,
col INTEGER not null,
content TEXT NOT NULL
);
#2
If you're just loading and saving it, not querying it much, I would recommend an XML field for the entire sheet, or each page, or at least each row.
如果您只是加载并保存它,而不是查询它,我会建议整个工作表,每个页面或至少每一行的XML字段。
My other question is, how are you querying this to populate your sheet? Are you using loops and opening separate queries for each row or column?
我的另一个问题是,您如何查询填充表格?您是否正在使用循环并为每行或每列打开单独的查询?
Your design may be suboptimal, but it actually should be performing decently for the data set you're describing, IMHO.
你的设计可能不是最理想的,但它实际上应该适合你所描述的数据集,恕我直言。
#3
Considering that Excel is widely considered to be the default tool for a database, I'm not sure there's anything about the kinds of data that is stored in spreadsheets except it's a single table and there's a limit on the number of rows.
考虑到Excel被广泛认为是数据库的默认工具,我不确定存储在电子表格中的数据种类是什么,除了它是单个表并且行数有限制。
I don't think there's enough distinction to say anything else than "It's the data that determines the structure, not the source."
我不认为除了“这是确定结构而非来源的数据”之外,还有其他的区别。