CODEWITHSUNDEEP
spreadsheetdatabase-design
Guido
Guido

Reputation: 47665

Database : best way to model a spreadsheet

I am trying to figure out the best way to model a spreadsheet (from the database point of view), taking into account :

I am thinking about something like :

class Cell(models.Model):
    column = models.ForeignKey(Column)
    row_number = models.IntegerField()    
    value = models.CharField(max_length=100)

class Column(models.Model):
    spreadsheet = models.ForeignKey(Spreadsheet)
    name = models.CharField(max_length=100)
    type = models.CharField(max_length=100)

class Spreadsheet(models.Model):
    name = models.CharField(max_length=100)
    creation_date = models.DateField()

Can you think about a better way to model a spreadsheet ? My approach allows to store the data as a String. I am worried about it being too slow to generate the CSV file.

Upvotes: 7

Views: 4115

Answers (5)

Amy Z.
Amy Z.

Reputation: 1

That is a good question that calls for many answers, depending how you approach it, I'd love to share an opinion with you. This topic is one the various we searched about at Zenkit, we even wrote an article about, we'd love your opinion on it: https://zenkit.com/en/blog/spreadsheets-vs-databases/

Upvotes: -1

user1228
user1228

Reputation:

Databases aren't designed for this. But you can try a couple of different ways.

The naiive way to do it is to do a version of One Table To Rule Them All. That is, create a giant generic table, all types being (n)varchars, that has enough columns to cover any forseeable spreadsheet. Then, you'll need a second table to store metadata about the first, such as what Column1's spreadsheet column name is, what type it stores (so you can cast in and out), etc. Then you'll need triggers to run against inserts that check the data coming in and the metadata to make sure the data isn't corrupt, etc etc etc. As you can see, this way is a complete and utter cluster. I'd run screaming from it.

The second option is to store your data as XML. Most modern databases have XML data types and some support for xpath within queries. You can also use XSDs to provide some kind of data validation, and xslts to transform that data into CSVs. I'm currently doing something similar with configuration files, and its working out okay so far. No word on performance issues yet, but I'm trusting Knuth on that one.

The first option is probably much easier to search and faster to retrieve data from, but the second is probably more stable and definitely easier to program against.

It's times like this I wish Celko had a SO account.

Upvotes: 3

Aleris
Aleris

Reputation: 8059

The best solution greatly depends of the way the database will be used. Try to find a couple of top use cases you expect and then decide the design. For example if there is no use case to get the value of a certain cell from database (the data is always loaded at row level, or even in group of rows) then is no need to have a 'cell' stored as such.

Upvotes: 1

Steven A. Lowe
Steven A. Lowe

Reputation: 61223

from a relational viewpoint:

Spreadsheet <-->> Cell : RowId, ColumnId, ValueType, Contents

there is no requirement for row and column to be entities, but you can if you like

Upvotes: 4

Turnkey
Turnkey

Reputation: 9406

You may want to study EAV (Entity-attribute-value) data models, as they are trying to solve a similar problem.

Entity-Attribute-Value - Wikipedia

Upvotes: 2

Related Questions