Common data model mistakes made by startups

> Obviously you can SELECT * INTO FROM .. if you're just temporarily inserting data

Okay, bad example. I should have specified: Insert into an existing table, add the missing columns automatically. There are some special-cases where most database engines can automatically generate schemas, and "SELECT... INTO" is one of those few. Usually only allowed with an empty destination table.

> I'm not sure I see the value in automatically importing arbitrary data into a schemad database object.

There are lots of use-cases for this. You might not have them, but other people do all the time. Just about any large-scale enterprise software needs to be extensible in the field, for example. Think the likes of SAP or Siebel. I've seen similar problems crop up in CMDBs, job and ticket management software, etc... Famously, Jira is slow precisely because it is so bad at handling this kind of extensibility efficiently.

> Are you suggesting that INSERT/UPDATE statements also have the ability to modify objects?

Not necessarily, although that could be an option. What I mean is that changes to the table schema should be made using insert/update/delete statements, where the only "data" is things such as the column names, types, constraints, etc...

> That sounds like it would add complexity without much gain as opposed to just running and alter table query.

It would dramatically reduce complexity, removing an entire language from database engines, along with all the associated vendor-specific syntax, quirks, and limitations.

In fact, the same "schema" that is used in the wire protocol could be directly equal to the actual schema, and its update language. So if you get back a query result (with data), and want to create a copy of that schema elsewhere (e.g.: a local cache database), then you just take the "header" from the result set and "insert" it into the destination database schema. No conversion, no escaping, nothing.

Seriously: Try this as an exercise. Use Java or C#, write a select statement from a query (that has join, views, etc...), and then write the code that generates a table to cache this data in a separate, local database instance.

Do it. Sit down and give it a good go. You won't appreciate how hard this is until you do!

Now write the code to update the cache table dynamically if the source table changes. Assume that table has a petabyte of data. (That's why it's a table, it's a cache for something that's far too big to fit in memory!)

Now write the code to do all of the above with foreign constraints.

Good luck!

> I'm not sure I'm convinced that we can have the cake and eat it

There is a reason NoSQL databases exist and are wildly popular. It's not that "schemaless" is truly better so often, it's more that modifying the schema in most DBMS offerings is so fiddly that it's essentially impossible to do programmatically.

> or inserted the data as JSON

NoSQL in a nutshell! You haven't solved the problem, you've given up and resorted to schemaless tables instead, exactly the "bad" example above with all of its limitations and issues.

I've seen some good attempts at solving this problem, but they barely scratch the surface.

> we didn't know enough about our product direction to make any truly informed decisions.

Bingo. Very few programmers are working on projects that they have many years of domain expertise in, so the data models we come up with are always going to be limited to our experience in the here and now. It's one thing to organize highly technical code around things like graph algorithms are combining multiple b-trees in a single operation to lookup data, it's a whole different thing to tackle line-of-business problems where things are not so well defined (even to the in-house domain experts).

Yeah but it's also not unusual to have shared accounts for manual testing in prod, or to write automated smoke tests that run in prod after a deploy...

I'm not sure how to get around this, actually. Any production service of a certain scale is going to have some amount of fake activity caused by debugging, monitoring, testing, feature demos to clients/investors/internal stakeholders... It seems naive to tell an engineering team "no test accounts in prod ever because it makes analytics harder."

I'm not quite sure what "master data" means in English (a non-native language to me) but Wikipedia tells me that it's "data about the business entities that provide context for business transactions" (and lists examples that sound relevant for this situation to me). Based on that I'm inclined to think that this would qualify.

We just have a live clone in Dev, updated monthly, and a dev instance of the front end to use it. Sometimes monthly is too long, so a DBA will run a manual update in off-peak times. Queries that don't write data back can be moved directly to prod, though we also have an ODS with denormalized data for easier creation of reports & analysis. And changes that significantly write back to the DB are moved to test first, then to prod. Sometimes different people have things going on and that requires different timing or a clean copy of dev or test, and we'll temporarily spin up another instance.

To be fair, the above description paints a better picture than we have in reality. There's nuances and edge cases. But prod is kept pretty clean. Most of the problems we have are related to upgrades-- these are enterprise apps that all use Oracle, and the latest updates for one might require a particulate version of Oracle, but another app will be in conflict with that version. So a lot of the DBA work involves wrangling support from vendors on how to work around these. You'd think an app using Oracle 12c would run fine if you upgrade to 13c, but no it doesn't.