Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

The task

I need to store the some values in sequence in the database. The data looks just like a sequence of numbers. Here is an example of the data:

            Val_1   Val_2   Val_3   ...     Val_145
Record 1        6       8       4                 5
Record 2        2       5       6                 3
etc.

Now the thing is that there are a couple of actions that the user has to be able to do:

  • Insert columns (== e.g. the user needs to enter additional data in between Val_1 and Val_2 columns and inserts 5 columns there). The column headers are sequential though (val_2 always follows val_1, val_3 always follows val_2) thus only the values get shifted to the right - just like in Excel when you insert columns.
  • Delete column - same as with insert columns - column header always stay the same and values get deleted and values from the right get shifted to the left - again just like in excel.
  • View random chunks of values. E.g. view values from index 600 to index 1000

The three solutions

I came up with three solutions that can store those values, but every solution has some flaws. Here they are:

Store the index of value in the Value

Tables:

table Record
    id int
table Value
    record_id int
    index int
    value int

Pros: easy to find the needed values by using the index field (where index > 662 and index < 987). Cons: inserting and deleting value are horrible. If there are 1000 values for a record then inserting one value at index 500 will require 1 insert and 500 updates (to shift values after index 500). Same problem with deleting values.

Store references to previous value and the next value

Tables:

table Record
    id int
table Value
    record_id int
    value int
    next_value_id int
    prev_value_id int

Pros: easy to insert and delete values - inserting a value requires 1 insert and 2 updates (updating refs on adjacent values). We can even remove the prev_value_id to make it even better. Cons: finding values by index will be horrible. Finding range of values from 500 to 1000 will require us to go though all the values starting at value 1.

Store values as e.g. string in the record table

Tables:

table Record
    id int
    values_str text

Pros: this is just like parsing a text file. Most work is done in code. Feels like a simple and straightforward way to go. Cons: does not feel right. This may look and feel good, but something about this design smells bad. I have a bad feeling about this.

The question

By know you probably already guessed the question - which way should I go? Is there a more sophisticated way of storing such data? Does any of the solutions I've thought of make any sense?

share|improve this question

3 Answers 3

up vote 1 down vote accepted

Here is one approach:

CREATE TABLE columns (
    id       INTEGER, 
    ordinal  INTEGER);

CREATE TABLE rows (
    id       INTEGER, 
    ordinal  INTEGER);

CREATE TABLE cells (
    rowid     INTEGER, 
    columnid  INTEGER, 
    value     TEXT);

This way, you will still need to +1/-1 the ordinals behind the new/deleted position, but fortunately you can do it all with a single statement, UPDATE rows SET ordinal = ordinal + 1 WHERE ordinal > 42. Although it updates many rows, the update statement should execute in less than a second.

Benefits:

  • Updating a rows table with N rows is lighter than updating a cells table with N*M rows.
  • Explicitly storing the ordinal means fast random access to an ordered subset of the data.
share|improve this answer

The problem with your question is that in databases (MSSQL Specific) data has no order. What i mean is that if you add a column to a table physically on disk that column is at the "End" of the table however with my SELECT statement or INSERT Statement I can set the order of the columns:

SELECT col3,col1,col2 ... INSERT tablea (col3,col1,col2) ...

If there is something that is natural in your excel sheet to sort the rows by (such as a date or increasing order ID) you can use the ORDER BY clause in your query to specify that.

If order matters to the data you must ensure that somewhere in your design that order is a column in the table (whether an ID or a date or some other meaningful column to provide your order). If you do not include that information than order is not guaranteed by the nature of relational databases.

The other issue is that if Users can delete and add things they get deleted and added for every user of the system. In general this is bad design and leads to a security nightmare. It is better to store every thing and then let the users query the database and manipulate the data in excel not mess with the source data or structure.

If you include a little more detail on what you are trying to accomplish a better answer could probably be provided and I would help you out if you could give more information towards the problem.

share|improve this answer
    
Just realized this was from 2 months ago. –  Wind Raven Feb 19 at 14:59
    
At the moment I basically have settled for the "Store the index of value in the Value" I described in my initial question since I still need fast read access and the linked list solution I thought of still required reindexing the whole data table and updating value indexes after e.g. column insertion. Your suggestion about using physical db table columns is quite interesting especially considering the fact that I am using db/schema per customer so that all users have their own private tables to work with. I will have to think about that. –  Jefim Feb 20 at 6:36

We used approach similar to Gudmundur Orn proposed.

Column ordering is very simple indeed, but main advantage is very fast queries on table data even on large tables. We had no problems with tables of about 1 million cells. Of course, with tuned indexes and queries.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.