python - What kind of database schema would I use to store users' transaction histories? -

i have series of python objects, each associated different user, e.g., obj1.userid = 1, obj2.userid = 2, etc. each object has transaction history expressed python dict, i.e., obj2.transaction_record = {"itemid": 1, "amount": 1, "date": "2011-01-04"} etc.

i need these objects persist, , transaction records may grow on time. therefore, i'm thinking of using orm sqlalchemy make happen.

what kind of database schema need specify store these objects in database?

i have 2 alternatives, neither seems correct thing do:

1. have different table each user:

create table user_id ( itemid int primary key, amount int, date character(10) );

2. store transaction history dict blob of json:

create table foo ( userid int primary key, trasaction_history blob);

is there cleaner way implement this?

your example seems entirely suited relational database. have one-to-one relationship between users , transactions, , sqlalchemy orm can express nicely.

class user(base):     __tablename__ = 'user'     id = column(integer, primary_key=true)     transaction_id = column(integer, foreignkey('transaction.id'))     transaction = relationship(         "transaction", uselist=false, backref=backref("user", uselist=false))   class transaction(base):     __tablename__ = 'transaction'     id = column(integer, primary_key=true)     amount = column(integer)     date = column(datetime, default=datetime.datetime.now) 

interface:

>>> t = transaction(amount=100) >>> u = user() >>> u.transaction = t >>> session.add(u) >>> session.commit() >>> u = session.query(user).first() >>> u.transaction.amount 100 >>> u.transaction.date datetime.datetime(2016, 1, 24, 16, 21, 38, 683959) 

i don't know why want have transaction expressed arbitrary python object, if has variable columns can encode transaction object json. sqlalchemy has ways of fully expressing , automating this:

class jsonencodeddict(typedecorator):     "represents immutable structure json-encoded string."      impl = varchar      def process_bind_param(self, value, dialect):         if value not none:             value = json.dumps(value)         return value      def process_result_value(self, value, dialect):         if value not none:             value = json.loads(value)         return value