5 ms·
Ask HK: How do you design a sql table schema when you expect it to change?
An example would be you're doing a prototype for a startup, but you're not sure what the complete schema would look like in the near future.
I would normally use nosql or postgresql and put them in a jsonb. However, in this scenario I am stuck with mysql 5.5-5.6 without ORMs
- nibs 10y ago1. Would be to use an ORM that supports PostgreSQL and MySQL, this has helped me with migrations in the past 2. Would be write really strong tests and migrate the test server database before you push update to production I am by no means an expert, but doing these things helped me successfully address database model iterations.
- kevindeasis 10y agoUnfortunately, I realized I shouldn't use ORMs. Using a load balancer will be a complementary with #2
- tracker1 10y agoA lot of the time, I'll use a mapped column that is xml/json, for additional fields that don't need to be queried directly against... A great example of this for me was with payments where it may be paypal, amazon payments or cc via another vendor. In this way, I don't need a bunch of custom fields that I'll never query on, just a generic additional field. In this way, I'll often write an abstraction class against my ORM to handle this, and then have properties that get/set against the underlying JSON data/object field. This is what you've done here... that said, any specific features of a single platform will mean pain in migration. If you must support multiple backends, then you should create an abstraction and a factory as appropriate. As much as I hated MS Enterprise Library's Data Application Blocks, supporting multiple backends was a problem it did help resolve. As nibs mentions, you should probably use an ORM that takes care of most of the differences between backends. TBH though, depending on the type of data you're using, I might start with a simple record store nosql server (rethinkdb, for example), and only migrate if/when needed.
- kevindeasis 10y agoYeah, I was thinking of using mysql+nosql. RethinkDb was my main choice, but I'd rather have someone take care of the backend for me like a DAAS, mostly to alleviate the pain of doing a project solo, but compose seems too expensive to use to prototype some ideas.
- tracker1 10y agoAgreed, if you don't mind getting locked into a platform, AWS, Azure, and Google all have options for varying no-sql/search and/or big-table data stores... but ymmv there.
- kevindeasis 10y agoI hear ya! I'm ultimately going to go with the strategy that you've mentioned. NoSql+Sql with Google and just write my ORMs and wrappers. Thanks for the advice!