12 ms·

Common data model mistakes made by startups

jasonhansel 5y ago
> Typically semi-structured data have schemas that are only enforced by convention Technically, in Postgres you can (kind of) enforce arbitrary schemas for semi-structured data using CHECK constraints. Unfortunately this isn't well-documented and NoSQL DBs often don't support similar mechanisms.
- ridaj 5y ago
  Seems likely that the enforced schema would then break things when someone updates the "live" schema without updating all of the checks littered through downstream tables...
- nerdponx 5y ago
  I'd love a document database that supports JSON Schema validation on read and write.
handrous 5y ago
> 5. The “right database for the job” syndrome I once saw something a little similar to this, except with one flavor of DB rather than several. A company you've likely heard of went hard for a certain Java graph database product, due to a combination of an internal advocate who seemed determined to be The GraphDB Guy and an engineering manager who was weirdly susceptible to marketing material. This because some of their data could be represented as graphs, so clearly a graph database is a good idea. However: the data for most of their products was tiny, rarely written, not even read that much really, even less commonly written concurrently, and was naturally sharded (with hard boundaries) among clients. Their use of that graph database product was plainly contributing to bugginess, operational pain, mediocre performance (it was reasonably fast... as long as you didn't want to both traverse a graph and fetch data related to that graph, then it was laughably slow) and low development velocity on multiple projects. Meanwhile, the best DB to deliver the features they wanted quickly & with some nice built-in "free" features for them (ability to control access via existing file sharing tools they had, for instance) was probably... SQLite.
- Too 5y ago
  Been through this as well. There was one database for relational data, one for logs, one for analytics, one for miscelaneous, one for binaries, one for time-series, one for key-values, one for caches and probably a lot more! Total nightmare. Nobody fully knew how operations, schemas, indexing or queries in any of them worked. Usually someone had managed to hack something together in a week and then the rest of the team just did minor changes to existing queries. Joining between the databases was also a fun exercise. I blame it all on docker. It's so easy to just docker-compose run grafana:latest, then dust off your hands and claim you have a database running. Articles from HN on how fancy setup Netflix have also contributes to this, you don't have the same ops capacity to replicate a FAANG stack. In the end all of it got replaced with only mongodb and firefighting went down to 0. Everybody in the team knew how to do everything, from new queries to migrations and backup-recovery. It's probably worse in every aspect on each task the specialized databases were solving, but it works good enough and often bringing a really good swiss-army-knife is better than having a caravan of specialized machines which each require special expertise.
rectang 5y ago
Metabase provides business analytics, and this list of "common mistakes" is weighted towards "choices which get in the way of business analytics". For example: > 1. Polluting your database with test or fake data > [...] By polluting your database with test data, you’ve introduced a tax on all analytics (and internal tool building) at your company.
- asperous 5y ago
  To your point, many of these could be addressed by making an analytics database copy of the transactional database, for example scrubbing test data and removing soft deletes in your etl. From my experience with metabase, this makes it easier to use anyway but it means you have to maintain an etl.
- BigJono 5y ago
  The end of this article is particularly weird. Is it really suggesting that a good general rule is to optimise for business metric queries (which sounds like something that would generally run daily during off peak hours or ad hoc when someone needs the data) over the most commonly run reads/updates (which sounds like something that will happen multiple times per minute for every active user)? I feel like I'm missing something because that seems insane to me.
  - kwertyoowiyop 5y ago
    Consider the source. The barber is suggesting optimizing for haircuts.
pyrophane 5y ago
I think the biggest mistake some startups make wrt their data model is not really thinking about it at all. The data model winds up being the byproduct of all the features they've implemented and the framework and the libraries they've used, rather than something that was deliberately designed.
- taeric 5y ago
  Oddly, I feel the opposite is also a trap. A carefully crafted data model often stalls out compared to a grown one.
- allie1 5y ago
  I think it’s a mistake that they don’t revisit it occasionally, and if necessary pull the trigger on a new schema + migration scripts. Some early mistakes just can’t be solved without a do-over, and from a recent experience, it ends up being less work than maintaining a flawed schema.
  - vosper 5y ago
    This is the place I work at. The data model was designed with a narrow focus. When that turned out to not be viable, the company moved into an adjacent and much larger market. But the names never changed, and the subtle differences between the two worlds was never addressed. So now our application is full of terminology and restrictions that confuse our customers, and our database doesn’t match anyone’s mental model of what the application does. It’s all workable, but IMO we’ve paid (and pay) a not-insignificant price in productivity and complexity because we never took the time to fix these things. At this point a ground-up rebuild is probably going to be no slower than trying to update the existing app. Neither will be cheap.
    - allie1 5y ago
      I hear ya. Both would probably cost the same, rebuild probably more, today, but it’s still cheaper in the long run. Unless the business goes back to what it was, it will keep diverging from the current terminology. No manager wants to hear it, but taking a 3-6 month breather to address tech debt like this is worth its weight in gold.
konha 5y ago
> On the flip side, soft deletes require every single read query to exclude deleted records. You can use partial indexes to only index non-deleted rows. If you are worried about having to remember to exclude deleted rows from queries: Use a view to abstract away the implementation detail from your analytics queries.
- ako 5y ago
  You can also use the index to cluster database blocks of the table based on the index (postgres => cluster command). This means all active records will be written to the same database blocks, and all deleted records will be kept in separate database blocks. This can speed up queries that need to access a lot of active records. This is a good alternative to moving deleted records from an active table to a deleted table.
ridaj 5y ago
I would personally add: - Having informal metrics and dimension definitions: you throw together something quick and dirty and then realize there's something semantically broken about your data definitions or unevenness. For example your Android app and iOS apps report "countries" differently, or they have meaningfully different notions of "active users" - Not anticipating backfill/restatement needs. Bugs in logging and analytics stacks happen as much as anywhere else, so it's important to plan for backfills. Without a plan, backfills can be major fire drills or impossible. - Being over-attentive to ratio metrics (CTR, conversion rates) which are typically difficult to diagnose (step 1 figure out whether the numerator or the denominator is the problem). Ratio metrics can be useful to rank N alternatives (eg campaign keywords) but absolute metrics are usually more useful for overall day to day monitoring. - Overlooking the usefulness of very simple basic alerting. It's common for bugs to cause a metric to go to zero, or to be double counted, or to not be updated with recent data, but often times even these highly obvious problems don't get detected until manual inspection.
- void_mint 5y ago
  > - Not anticipating backfill needs. Bugs in logging and analytics stacks happen, so it's important to plan for backfills. Without a plan, backfills can be major fire drills or impossible. This matches my experience. Building tools that allow you to rebuild some or all of a dataset with minimal headache make any individual task much easier. Both in terms of safety, and in terms of things like branching/dev environments.
  - cerved 5y ago
    what's the relation to bugs in logging and analytics? I'm not sure I see it also, is there a good resource on how to backfill?
    - ridaj 5y ago
      For example, your app logs clicks on the "submit" button, but there's a bug in your UX and the button is clickable/tappable multiple times while the form is being processed, instead of being disabled while being processed. Some users are tap-happy and will tap many times thus counting for multiple submissions. If that's how you count actions in your dashboards it will overcount. In terms of resources, I'm not aware of a one-size-fits-all approach... the most basic would be to define upfront what the playbook is for making backfills, and testing it once in a while if you don't get the natural opportunity to do it.
brylie 5y ago
If your company has a subscription business model, keep a history of user's subscriptions. They change over time and it is likely you will need to measure popularity and profitability of product offerings over time. Please don't force your analytics team to rely on event logs to reconstruct a subscription history.
- maneesh 5y ago
  Stripe manages this extremely well
  - brylie 5y ago
    That's a good point. Most subscription service providers, like Stripe, Chargrbee, Braintree, etc, use a fairly conventional one-to-many data architecture for Customers and Subscriptions. Just take care to use the subscription service provider data model how it is intended. It is possible to design your integration in a way that goes against the grain and end up with gaps in your data. For example, by re-using a single subscription instance per customer and changing it's properties when the customer down/upgrades rather than creating a new Subscription instance.
- jabo 5y ago
  This. You want to capture timestamps as users downgrade, upgrade, change quantity, churn, etc. If you have a status field, timestamp the changes to it. This way it’s easy to get the state of the world on any given day, which is a common analysis that’s done to study behavior of cohorts of subscriptions over time.
- nerdponx 5y ago
  I first learned what an "audit log" was because I had to use an audit log to figure out the states of record in the database at a time in the past, because some specific pieces of data were being lost in the "soft-update" database setup.
Pxtl 5y ago
How do you reconcile the first bullet point (polluting data with test data) vs Test In Production being the modern trend? Those sound irreconcilable.
- allie1 5y ago
  Include a cleanup step after each test?
  - jerrysievert 5y ago
    I find ROLLBACK to be a good fix for that.
- edgyquant 5y ago
  Doesn’t this mean beta testing in prod? Development, at least everywhere I’ve worked, takes place on a separate db. For instance where I work atm we copy prod data to a staging db every couple of months and develop/test new features there before rolling them out. Any data coming from the beta test, in prod, is not really test data it is prod data and I don’t see why you’d want to remove it.
- anonytrary 5y ago
  is_fake column should do it.
deleted 5y ago
[deleted]
dugmartin 5y ago
I would add to their semi structured data fields section a suggestion to add a version or type key. Otherwise your code consuming those field may grow over time to a bunch of conditionals to figure what is in the json.
jayd16 5y ago
Whats the best way to construct a session? >The exact definition of what comprises a session typically changes as the app itself changes. Isn't this an argument for post-hoc reconstruction? You can consistently re-run your analytics. If the definition changes in code, your persisted data becomes inconsistent, no?
eterm 5y ago
A more common thing I think is just trying to collect and hoard too much data. Most of even these worries such as soft deletes disappear if you're not trying to keep every scrap of data you can. Focus on the core business requirements and competencies and you likely don't need to store the minutae of every interaction forever.
rm999 5y ago
>Soft deletes This section is totally wrong IMO. What is the alternative? "Hard" deleting records from a table is usually a bad idea (unless it is for legal reasons), especially if that table's primary key is a foreign key in another table - imagine deleting a user and then having no idea who made an order. Setting a deleted/inactive flag is by far the least of two evils. >when multiplied across all the analytics queries that you’ll run, this exclusion quickly starts to become a serious drag I disagree, modern analytics databases filter cheaply and easily. I have scaled data orgs 10-50x and never seen this become an issue. And if this is really an issue, you can remove these records in a transform layer before it hits your analytics team, e.g. in your data warehouse. >soft deletes introduce yet another place where different users can make different assumptions Again, you can transform these records out.
- watermelon0 5y ago
  Hard deletes most likely need to be supported, due to legal or contractual obligations. Designing with this in mind, makes everything a lot easier in the long run.
  - rm999 5y ago
    I’ve always NULL’d values, not deleted rows. E.g. GDPR request? NULL out all identifying information, but keep the record. As long as your primary key has no business meaning you should never have to delete the row of a table.
    - FigmentEngine 5y ago
      INAL, but... you might want to revisit that code. article 17, right to erasure is about erasure of personal data, not about making non-indentifiable. of course they dont define erase or delete :-) (edit: typo)
      - Aperocky 5y ago
        well to me the transaction is the same as deleting a record and populating a NULL record. I don't see why the law should care in any way about a company populating NULL records.
giovannibonetti 5y ago
> Queries for business metrics are usually scattered, written by many people, and generally much less controlled. So do what you can to make it easy for your business to get the metrics it needs to make better decisions. A simple but useful thing is setting the database default time zone match the one where most of your team is (instead of UTC). This reduces the chance your metrics are wrong because you forgot to set the time zone when extracting the date of a timestamp.
- sethammons 5y ago
  We went this route and ended up with a db set to pst and some servers based on Chicago time. Endless time bugs. Pick one timezone for everything or just use unix timestamps.
- xyzzy_plugh 5y ago
  I cannot overstate how bad this advice is. Everything should be UTC by default. You can explicitly use timestamp with timezones and frankly it's trivial to query something like midnight-to-midnight PST. Your team should learn this as early as possible. Build tooling around this, warn users, hell, educate them, but don't set up foot-guns like non-UTC. If I see a timestamp without a timezone, it must always be UTC. To do anything else is to introduce insanity.
  - higeorge13 5y ago
    This. I once joined a company with local timezone per deployment and it was a nightmare. Not only in terms of development and debugging, but even for all the support tools required and the numerous bugs we found. I insisted that all the tools that were going to be installed under my watch would be UTC, and never experienced any time issue on them.
- dehrmann 5y ago
  I disagree on this because handling DST is error-prone.
ineedasername 5y ago
Polluting your database with test or fake data Maybe I've been spoiled, but isn't it common to have dev, test, and prod instances? Possibly multiples of the former 2?
- klowner 5y ago
  I do this for personal projects, that seems like basic obvious stuff, IMHO.
- anchochilis 5y ago
  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."
  - ineedasername 5y ago
    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.
elchief 5y ago
Some enterprise data model links here: https://dba.stackexchange.com/questions/12991/ready-to-use-database-models-example https://dba.stackexchange.com/questions/12991/ready-to-use-d... Instead of soft deletes, move records to a history table I agree w session issue. Had to rebuild sessions before and is a pita compared to just recording them at source
- konfusinomicon 5y ago
  Good list in there. Len silverstons data model resource books are amazing.. especially volume 3. Reading that book and getting to the point where I actually understood the most generalized patterns in it was a total game changer for me
jerrysievert 5y ago
the one that is missing for me, that is my personal pet peeve: an index for every column in the database. then wondering why inserts are slow. seriously?
- cerved 5y ago
  why would anyone ever want to do that?
  - jerrysievert 5y ago
    Their “justification” was that they wanted to be able to sort by any column.
    - cerved 5y ago
      my condolences
worik 5y ago
In my experience I would add: Building systems out of "lego blocks". It is possible to get all the pieces that are needed to build a data server for a enterprise pre built form cloud providers. Then plumb them together so the mostly work. When the heat comes on and peopel are using it for real and it must scale (even a little) it blows up horribly. The "leggo bricks" save a lot of time and money, and mean that people with only half a clue can build large impressive looking systems, but in the end people like ,e are picking up the pieced
cjfd 5y ago
It sounds like quite a few of the problems that are mentioned here can be ameliorated using views.
nivertech 5y ago
There are advantages for soft deletes for CRUD architecture, but are there any for CQRS/ES (Event Sourcing)? I guess if your read model is based on RDBMS then it makes sense, otherwise it depends on the database system in question (i.e. some NoSQL databases like C*[1] and Riak[2] are implementing deletes by writing special tombstone values, which is kind of soft-delete but on the implementation level - but you can't easily restore the data like in case of RDBMS). [1] https://thelastpickle.com/blog/2016/07/27/about-deletes-and-tombstones.html https://thelastpickle.com/blog/2016/07/27/about-deletes-and-... [2] https://docs.riak.com/riak/kv/latest/using/reference/object-deletion/index.html https://docs.riak.com/riak/kv/latest/using/reference/object-...
FriedrichN 5y ago
I have seen so many people argue against soft deletes over the years. But I have also had so many instances where users 'accidentally' deleted a bunch of items and then call support to ask if there are any backups. And then I'll have to reconstruct the data from yesterday's backup plus today's changes. A soft delete will take care of this. And no amount of "are you really really really sure you want to delete this?" confirmations are going to fix this. You could require the whole Spongebob Squarepants ravioli ravioli give me the formuoli song and dance and people will still delete hundreds or thousands of records by accident.
- philprx 5y ago
  On this case, one way is to make a past_ or deleted_$tablename where you insert the deleted row before deleting it from production table. This way you can watch post mortem, restore etc... AND it's not soft delete since the data is really gone from the production table, therefore no query tweaking Only thing: you need to really delete when GDPR related deletion is requested.
  - FriedrichN 5y ago
    >On this case, one way is to make a past_ or deleted_$tablename where you insert the deleted row before deleting it from production table. The problem with this that it gets really cumbersome if you have a complex system of tables that depend on the main table, you'll end up having to make deleted/archived versions of all those tables. In that case it's easier to have a deleted/archived flag in the main table.
intricatedetail 5y ago
I am happy that on so many projects we rejected the kool aid and just used postgres and redis. Can't remember ever troubleshooting these.

> Typically semi-structured data have schemas that are only enforced by convention Technically, in Postgres you can (kind of) enforce arbitrary schemas for semi-structured data using CHECK constraints. Unfortunately this isn't well-documented and NoSQL DBs often don't support similar mechanisms.

ridaj 5y ago
Seems likely that the enforced schema would then break things when someone updates the "live" schema without updating all of the checks littered through downstream tables...
nerdponx 5y ago
I'd love a document database that supports JSON Schema validation on read and write.

handrous 5y ago

> 5. The “right database for the job” syndrome I once saw something a little similar to this, except with one flavor of DB rather than several. A company you've likely heard of went hard for a certain Java graph database product, due to a combination of an internal advocate who seemed determined to be The GraphDB Guy and an engineering manager who was weirdly susceptible to marketing material. This because some of their data could be represented as graphs, so clearly a graph database is a good idea. However: the data for most of their products was tiny, rarely written, not even read that much really, even less commonly written concurrently, and was naturally sharded (with hard boundaries) among clients. Their use of that graph database product was plainly contributing to bugginess, operational pain, mediocre performance (it was reasonably fast... as long as you didn't want to both traverse a graph and fetch data related to that graph, then it was laughably slow) and low development velocity on multiple projects. Meanwhile, the best DB to deliver the features they wanted quickly & with some nice built-in "free" features for them (ability to control access via existing file sharing tools they had, for instance) was probably... SQLite.

Too 5y ago
Been through this as well. There was one database for relational data, one for logs, one for analytics, one for miscelaneous, one for binaries, one for time-series, one for key-values, one for caches and probably a lot more! Total nightmare. Nobody fully knew how operations, schemas, indexing or queries in any of them worked. Usually someone had managed to hack something together in a week and then the rest of the team just did minor changes to existing queries. Joining between the databases was also a fun exercise. I blame it all on docker. It's so easy to just docker-compose run grafana:latest, then dust off your hands and claim you have a database running. Articles from HN on how fancy setup Netflix have also contributes to this, you don't have the same ops capacity to replicate a FAANG stack. In the end all of it got replaced with only mongodb and firefighting went down to 0. Everybody in the team knew how to do everything, from new queries to migrations and backup-recovery. It's probably worse in every aspect on each task the specialized databases were solving, but it works good enough and often bringing a really good swiss-army-knife is better than having a caravan of specialized machines which each require special expertise.

rectang 5y ago

Metabase provides business analytics, and this list of "common mistakes" is weighted towards "choices which get in the way of business analytics". For example: > 1. Polluting your database with test or fake data > [...] By polluting your database with test data, you’ve introduced a tax on all analytics (and internal tool building) at your company.

asperous 5y ago
To your point, many of these could be addressed by making an analytics database copy of the transactional database, for example scrubbing test data and removing soft deletes in your etl. From my experience with metabase, this makes it easier to use anyway but it means you have to maintain an etl.
BigJono 5y ago
The end of this article is particularly weird. Is it really suggesting that a good general rule is to optimise for business metric queries (which sounds like something that would generally run daily during off peak hours or ad hoc when someone needs the data) over the most commonly run reads/updates (which sounds like something that will happen multiple times per minute for every active user)? I feel like I'm missing something because that seems insane to me.
- kwertyoowiyop 5y ago
  Consider the source. The barber is suggesting optimizing for haircuts.

pyrophane 5y ago

I think the biggest mistake some startups make wrt their data model is not really thinking about it at all. The data model winds up being the byproduct of all the features they've implemented and the framework and the libraries they've used, rather than something that was deliberately designed.

taeric 5y ago
Oddly, I feel the opposite is also a trap. A carefully crafted data model often stalls out compared to a grown one.
allie1 5y ago
I think it’s a mistake that they don’t revisit it occasionally, and if necessary pull the trigger on a new schema + migration scripts. Some early mistakes just can’t be solved without a do-over, and from a recent experience, it ends up being less work than maintaining a flawed schema.
- vosper 5y ago
  This is the place I work at. The data model was designed with a narrow focus. When that turned out to not be viable, the company moved into an adjacent and much larger market. But the names never changed, and the subtle differences between the two worlds was never addressed. So now our application is full of terminology and restrictions that confuse our customers, and our database doesn’t match anyone’s mental model of what the application does. It’s all workable, but IMO we’ve paid (and pay) a not-insignificant price in productivity and complexity because we never took the time to fix these things. At this point a ground-up rebuild is probably going to be no slower than trying to update the existing app. Neither will be cheap.
  - allie1 5y ago
    I hear ya. Both would probably cost the same, rebuild probably more, today, but it’s still cheaper in the long run. Unless the business goes back to what it was, it will keep diverging from the current terminology. No manager wants to hear it, but taking a 3-6 month breather to address tech debt like this is worth its weight in gold.

konha 5y ago

> On the flip side, soft deletes require every single read query to exclude deleted records. You can use partial indexes to only index non-deleted rows. If you are worried about having to remember to exclude deleted rows from queries: Use a view to abstract away the implementation detail from your analytics queries.

ako 5y ago
You can also use the index to cluster database blocks of the table based on the index (postgres => cluster command). This means all active records will be written to the same database blocks, and all deleted records will be kept in separate database blocks. This can speed up queries that need to access a lot of active records. This is a good alternative to moving deleted records from an active table to a deleted table.

ridaj 5y ago

I would personally add: - Having informal metrics and dimension definitions: you throw together something quick and dirty and then realize there's something semantically broken about your data definitions or unevenness. For example your Android app and iOS apps report "countries" differently, or they have meaningfully different notions of "active users" - Not anticipating backfill/restatement needs. Bugs in logging and analytics stacks happen as much as anywhere else, so it's important to plan for backfills. Without a plan, backfills can be major fire drills or impossible. - Being over-attentive to ratio metrics (CTR, conversion rates) which are typically difficult to diagnose (step 1 figure out whether the numerator or the denominator is the problem). Ratio metrics can be useful to rank N alternatives (eg campaign keywords) but absolute metrics are usually more useful for overall day to day monitoring. - Overlooking the usefulness of very simple basic alerting. It's common for bugs to cause a metric to go to zero, or to be double counted, or to not be updated with recent data, but often times even these highly obvious problems don't get detected until manual inspection.

void_mint 5y ago
> - Not anticipating backfill needs. Bugs in logging and analytics stacks happen, so it's important to plan for backfills. Without a plan, backfills can be major fire drills or impossible. This matches my experience. Building tools that allow you to rebuild some or all of a dataset with minimal headache make any individual task much easier. Both in terms of safety, and in terms of things like branching/dev environments.
- cerved 5y ago
  what's the relation to bugs in logging and analytics? I'm not sure I see it also, is there a good resource on how to backfill?
  - ridaj 5y ago
    For example, your app logs clicks on the "submit" button, but there's a bug in your UX and the button is clickable/tappable multiple times while the form is being processed, instead of being disabled while being processed. Some users are tap-happy and will tap many times thus counting for multiple submissions. If that's how you count actions in your dashboards it will overcount. In terms of resources, I'm not aware of a one-size-fits-all approach... the most basic would be to define upfront what the playbook is for making backfills, and testing it once in a while if you don't get the natural opportunity to do it.

brylie 5y ago

If your company has a subscription business model, keep a history of user's subscriptions. They change over time and it is likely you will need to measure popularity and profitability of product offerings over time. Please don't force your analytics team to rely on event logs to reconstruct a subscription history.

maneesh 5y ago
Stripe manages this extremely well
- brylie 5y ago
  That's a good point. Most subscription service providers, like Stripe, Chargrbee, Braintree, etc, use a fairly conventional one-to-many data architecture for Customers and Subscriptions. Just take care to use the subscription service provider data model how it is intended. It is possible to design your integration in a way that goes against the grain and end up with gaps in your data. For example, by re-using a single subscription instance per customer and changing it's properties when the customer down/upgrades rather than creating a new Subscription instance.
jabo 5y ago
This. You want to capture timestamps as users downgrade, upgrade, change quantity, churn, etc. If you have a status field, timestamp the changes to it. This way it’s easy to get the state of the world on any given day, which is a common analysis that’s done to study behavior of cohorts of subscriptions over time.
nerdponx 5y ago
I first learned what an "audit log" was because I had to use an audit log to figure out the states of record in the database at a time in the past, because some specific pieces of data were being lost in the "soft-update" database setup.

Pxtl 5y ago

How do you reconcile the first bullet point (polluting data with test data) vs Test In Production being the modern trend? Those sound irreconcilable.

allie1 5y ago
Include a cleanup step after each test?
- jerrysievert 5y ago
  I find ROLLBACK to be a good fix for that.
edgyquant 5y ago
Doesn’t this mean beta testing in prod? Development, at least everywhere I’ve worked, takes place on a separate db. For instance where I work atm we copy prod data to a staging db every couple of months and develop/test new features there before rolling them out. Any data coming from the beta test, in prod, is not really test data it is prod data and I don’t see why you’d want to remove it.
anonytrary 5y ago
is_fake column should do it.

deleted 5y ago

[deleted]

dugmartin 5y ago

I would add to their semi structured data fields section a suggestion to add a version or type key. Otherwise your code consuming those field may grow over time to a bunch of conditionals to figure what is in the json.

jayd16 5y ago

Whats the best way to construct a session? >The exact definition of what comprises a session typically changes as the app itself changes. Isn't this an argument for post-hoc reconstruction? You can consistently re-run your analytics. If the definition changes in code, your persisted data becomes inconsistent, no?

eterm 5y ago

A more common thing I think is just trying to collect and hoard too much data. Most of even these worries such as soft deletes disappear if you're not trying to keep every scrap of data you can. Focus on the core business requirements and competencies and you likely don't need to store the minutae of every interaction forever.

rm999 5y ago

>Soft deletes This section is totally wrong IMO. What is the alternative? "Hard" deleting records from a table is usually a bad idea (unless it is for legal reasons), especially if that table's primary key is a foreign key in another table - imagine deleting a user and then having no idea who made an order. Setting a deleted/inactive flag is by far the least of two evils. >when multiplied across all the analytics queries that you’ll run, this exclusion quickly starts to become a serious drag I disagree, modern analytics databases filter cheaply and easily. I have scaled data orgs 10-50x and never seen this become an issue. And if this is really an issue, you can remove these records in a transform layer before it hits your analytics team, e.g. in your data warehouse. >soft deletes introduce yet another place where different users can make different assumptions Again, you can transform these records out.

watermelon0 5y ago
Hard deletes most likely need to be supported, due to legal or contractual obligations. Designing with this in mind, makes everything a lot easier in the long run.
- rm999 5y ago
  I’ve always NULL’d values, not deleted rows. E.g. GDPR request? NULL out all identifying information, but keep the record. As long as your primary key has no business meaning you should never have to delete the row of a table.
  - FigmentEngine 5y ago
    INAL, but... you might want to revisit that code. article 17, right to erasure is about erasure of personal data, not about making non-indentifiable. of course they dont define erase or delete :-) (edit: typo)
    - Aperocky 5y ago
      well to me the transaction is the same as deleting a record and populating a NULL record. I don't see why the law should care in any way about a company populating NULL records.

giovannibonetti 5y ago

> Queries for business metrics are usually scattered, written by many people, and generally much less controlled. So do what you can to make it easy for your business to get the metrics it needs to make better decisions. A simple but useful thing is setting the database default time zone match the one where most of your team is (instead of UTC). This reduces the chance your metrics are wrong because you forgot to set the time zone when extracting the date of a timestamp.

sethammons 5y ago
We went this route and ended up with a db set to pst and some servers based on Chicago time. Endless time bugs. Pick one timezone for everything or just use unix timestamps.
xyzzy_plugh 5y ago
I cannot overstate how bad this advice is. Everything should be UTC by default. You can explicitly use timestamp with timezones and frankly it's trivial to query something like midnight-to-midnight PST. Your team should learn this as early as possible. Build tooling around this, warn users, hell, educate them, but don't set up foot-guns like non-UTC. If I see a timestamp without a timezone, it must always be UTC. To do anything else is to introduce insanity.
- higeorge13 5y ago
  This. I once joined a company with local timezone per deployment and it was a nightmare. Not only in terms of development and debugging, but even for all the support tools required and the numerous bugs we found. I insisted that all the tools that were going to be installed under my watch would be UTC, and never experienced any time issue on them.
dehrmann 5y ago
I disagree on this because handling DST is error-prone.

ineedasername 5y ago

Polluting your database with test or fake data Maybe I've been spoiled, but isn't it common to have dev, test, and prod instances? Possibly multiples of the former 2?

klowner 5y ago
I do this for personal projects, that seems like basic obvious stuff, IMHO.
anchochilis 5y ago
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."
- ineedasername 5y ago
  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.

elchief 5y ago

Some enterprise data model links here: https://dba.stackexchange.com/questions/12991/ready-to-use-database-models-example https://dba.stackexchange.com/questions/12991/ready-to-use-d... Instead of soft deletes, move records to a history table I agree w session issue. Had to rebuild sessions before and is a pita compared to just recording them at source

konfusinomicon 5y ago
Good list in there. Len silverstons data model resource books are amazing.. especially volume 3. Reading that book and getting to the point where I actually understood the most generalized patterns in it was a total game changer for me

jerrysievert 5y ago

the one that is missing for me, that is my personal pet peeve: an index for every column in the database. then wondering why inserts are slow. seriously?

cerved 5y ago
why would anyone ever want to do that?
- jerrysievert 5y ago
  Their “justification” was that they wanted to be able to sort by any column.
  - cerved 5y ago
    my condolences

worik 5y ago

In my experience I would add: Building systems out of "lego blocks". It is possible to get all the pieces that are needed to build a data server for a enterprise pre built form cloud providers. Then plumb them together so the mostly work. When the heat comes on and peopel are using it for real and it must scale (even a little) it blows up horribly. The "leggo bricks" save a lot of time and money, and mean that people with only half a clue can build large impressive looking systems, but in the end people like ,e are picking up the pieced

cjfd 5y ago

It sounds like quite a few of the problems that are mentioned here can be ameliorated using views.

nivertech 5y ago

There are advantages for soft deletes for CRUD architecture, but are there any for CQRS/ES (Event Sourcing)? I guess if your read model is based on RDBMS then it makes sense, otherwise it depends on the database system in question (i.e. some NoSQL databases like C*[1] and Riak[2] are implementing deletes by writing special tombstone values, which is kind of soft-delete but on the implementation level - but you can't easily restore the data like in case of RDBMS). [1] https://thelastpickle.com/blog/2016/07/27/about-deletes-and-tombstones.html https://thelastpickle.com/blog/2016/07/27/about-deletes-and-... [2] https://docs.riak.com/riak/kv/latest/using/reference/object-deletion/index.html https://docs.riak.com/riak/kv/latest/using/reference/object-...

FriedrichN 5y ago

I have seen so many people argue against soft deletes over the years. But I have also had so many instances where users 'accidentally' deleted a bunch of items and then call support to ask if there are any backups. And then I'll have to reconstruct the data from yesterday's backup plus today's changes. A soft delete will take care of this. And no amount of "are you really really really sure you want to delete this?" confirmations are going to fix this. You could require the whole Spongebob Squarepants ravioli ravioli give me the formuoli song and dance and people will still delete hundreds or thousands of records by accident.

philprx 5y ago
On this case, one way is to make a past_ or deleted_$tablename where you insert the deleted row before deleting it from production table. This way you can watch post mortem, restore etc... AND it's not soft delete since the data is really gone from the production table, therefore no query tweaking Only thing: you need to really delete when GDPR related deletion is requested.
- FriedrichN 5y ago
  >On this case, one way is to make a past_ or deleted_$tablename where you insert the deleted row before deleting it from production table. The problem with this that it gets really cumbersome if you have a complex system of tables that depend on the main table, you'll end up having to make deleted/archived versions of all those tables. In that case it's easier to have a deleted/archived flag in the main table.

intricatedetail 5y ago

I am happy that on so many projects we rejected the kool aid and just used postgres and redis. Can't remember ever troubleshooting these.