The best database design (model) for custom tables

Question

I am developing a web application using google appengine and django, but I think my problem is more general.

Users have the ability to create tables, look: tables are not presented as TABLES in the database. I will give you an example:

First form:
 Name of the the table: __________
 First column name: __________
 Second column name: _________
 ...

The number of columns is not fixed, but there is a maximum (for example, 100). The type in each column is the same.

Second form (after choosing a particular table the user can fill the table):
 column_name1: _____________
 column_name2: _____________
 ....

I am using this solution, but it is wrong:

class Table(db.Model):
    name = db.StringProperty(required = True)

class Column(db.Model):
    name = db.StringProperty(required = True)
    number = db.IntegerProperty()
    table = db.ReferenceProperty(table, collection_name="columns")

class Value(db.Model):
    time = db.TimeProperty()
    column = db.ReferenceProperty(Column, collection_name="values")

when I want to list a table, I take its columns and from each column I take their values:

    data = []
    for column in data.columns:
        column_data = []
        for value in column.values:
            column_data.append(value.time)
        data.append(column_data)
    data = zip(*data)

, - , , . ( ):

Table as I want:   as I will got:
a z c                 a e c
d e f                 d h f
g h i                 g z i

? , ListProperty?

Answer 1

, :

class Table(db.Model):
 name = db.StringProperty(required=True)
 owner = db.UserProperty()
 column_names = db.StringListProperty()

class Row(db.Model):
 values = db.ListProperty(yourtype)
 table = db.ReferenceProperty(Table, collection_name='rows')

: . , , , , .

Row, column_names, .

, .

emptor: , , . , , , , , , . , , .

, Expando...

, Row Expando, . , , , :

class Row(db.Expando):
    table = db.ReferenceProperty(Table, collection_name='rows')

    @staticmethod
    def __name_for_column_index(index):
        return "column_%d" % index

    def __getitem__(self, key):
        # Allows one to get at the columns of Row entities with
        # subscript syntax:
        # first_row = Row.get()
        # col1 = first_row[1]
        # col12 = first_row[12]
        value = None
        try:
            value = self.__dict__[Row.__name_for_column_index]
        catch KeyError:
            # The given column is not defined for this Row
            pass
        return value

    def __setitem__(self, key, value):
        # Allows one to set the columns of Row entities with
        # subscript syntax:
        # first_row = Row.get()
        # first_row[5] = "New values for column 5"

        self.__dict__[Row.__name_for_column_index] = value
        # In order to allow efficient multiple column changes,
        # the put() can go somewhere else.
        self.put()

Answer 2

IntegerProperty rowNumber , , Number.

Answer 3

, . , .

Answer 4

, : :

• table , -, . : table_name, column_name, column_order. column_order

• data -. : row_id, table_name, column_name, column_data. row_id , -.

Answer 5

LongBlob.

, , , : , . , , , . " ".

I would suggest simply formatting and storing all the data from one user in one column with a suitable type (for example, LongBlob). A format is an object with a list of columns and rows of a type. And you define an object in any language that you use to communicate with the database.

The tables in your (real) database will be: User int, TableNo int, Table Longblob. If user8 has 3 tables, you will have the following rows:

8, 1, objectcontaintingtable1;
8, 2, objectcontaintingtable2;
8, 3, objectcontaintingtable3;