Store spreadsheet kind of data in SQL Database

Question

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?

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.

My current database structure is as below.

Column Table 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)

Answer 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
);

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

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.

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

I don't think there's enough distinction to say anything else than "It's the data that determines the structure, not the source."