samedi 15 août 2015

SQLite create a table for each point or store as string

I need to store 3D points in a database (SQLite using SQLAlchemy). Each 3D point set would contain ~1000 points and I have up to hundreds of point sets. I have been using JSON to convert each of the point X, Y, Z coordinates to a large string then storing the 3 strings in a single table.

from db.base import Base
from sqlalchemy import Column, Integer, String

class PointData(Base):
    ''' Raw point data, cannot store array in sqlite '''
    #JSON strings 
    x_data = Column(String(), nullable=True)
    y_data = Column(String(), nullable=True)
    z_data = Column(String(), nullable=True)

The other option would be to create a table for each point in the point set - and I would end up with ~1000 tables. Which is a better method for database overall size and performance - large single tables or many more small tables?

Aucun commentaire:

Enregistrer un commentaire