
Ask HK: How do you design a sql table schema when you expect it to change? - kevindeasis
An example would be you&#x27;re doing a prototype for a startup, but you&#x27;re not sure what the complete schema would look like in the near future.<p>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
======
tracker1
A 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
Yeah, 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
Agreed, 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
I 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!

------
nibs
1\. 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
Unfortunately, I realized I shouldn't use ORMs.

Using a load balancer will be a complementary with #2

