Dennis Lee Bieber <wlfr...@ix.netcom.com> writes: > On Tue, 01 Mar 2022 08:35:05 +0100, Loris Bennett > <loris.benn...@fu-berlin.de> declaimed the following: > >>Thanks for the various suggestions. The data I need to store is just a >>dict with maybe 3 or 4 keys and short string values probably of less >>than 32 characters each per event. The traffic on the DB is going to be >>very low, creating maybe a dozen events a day, mainly triggered via a >>command-line interface, although I will probably set up one or two cron >>jobs, each of which might generate another 0 to maybe 5 records a day. >> >>I could go for JSON (or rather LONGSTRING, as JSON is just an alias for >>LONGSTRING, but JSON is not available on the version of MariaDB I am >>using). However, that seems like overkill, since I am never going to >>have to store anything near 4 GB in the field. So I should probably in >>fact just use say VARCHAR(255). >> >>WDYT? >> > > Having taken a few on-line short courses on database normalization and > SQL during my first lay-off, my view would be to normalize everything > first... Which, in your description, means putting that dictionary into a > separate table of the form (I also tend to define an autoincrement primary > key for all tables): > > DICTDATA(*ID*, _eventID_, dictKey, dictValue) > > where * delimits primary key, _ delimits foreign key to parent (event?) > record.
Ah, yes, you are right. That would indeed be the correct way to do it. I'll look into that. Up to now I was thinking I would only ever want to read out the dict in its entirety, but that's probably not correct. > Caveat: While I have a book on SQLAlchemy, I confess it makes no sense to > me -- I can code SQL joins faster than figuring out how to represent the > same join in SQLAlchemy. I currently can't code SQL joins fast anyway, so although doing it in SQLAlchemy is might be relatively slower, absolutely there's maybe not going to be much difference :-) Cheers, Loris -- This signature is currently under construction. -- https://mail.python.org/mailman/listinfo/python-list
