Common data model mistakes made by startups • Hacker News Board

Re: Common data model mistakes made by startups

#41

Post by kwertyoowiyop » Sat, May 22, 2021, 10:26 PM UTC

BigJono wrote: post #19

rectang wrote: post #4

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.

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…

Consider the source. The barber is suggesting optimizing for haircuts.

Re: Common data model mistakes made by startups

#42

Post by Tarq0n » Sat, May 22, 2021, 10:28 PM UTC

FigmentEngine wrote: post #34

rm999 wrote: post #30

Earlier quoted context omitted.

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.

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)

If you erase all identifying parts it stops meeting the definition of personal data. That should be sufficient.

Re: Common data model mistakes made by startups

#43

Post by andrewprock » Sat, May 22, 2021, 10:35 PM UTC

irrational wrote: post #28

rm999 wrote: post #23

>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 que…

> 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 Assuming you have constraints set up correctly (on delete no action or on delete restrict) then how could this ever happen? If you don’t have constraints set up correctly…

The chance that you don't have constraints set up correctly is indistinguishable from 100%.

Re: Common data model mistakes made by startups

#44

Post by ineedasername » Sat, May 22, 2021, 10:40 PM UTC

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?

Re: Common data model mistakes made by startups

#45

Post by quietbritishjim » Sat, May 22, 2021, 10:44 PM UTC

irrational wrote: post #28

rm999 wrote: post #23

>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 que…

> 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 Assuming you have constraints set up correctly (on delete no action or on delete restrict) then how could this ever happen? If you don’t have constraints set up correctly…

That would just make the data loss problem worse still. I realise OP just chose an arbitrary example, but if you really are talking about users and orders, and if you delete a user, then really deleting the records for their associated orders is even worse than losing track of who made them.

Re: Common data model mistakes made by startups

#46

Post by vericiab » Sat, May 22, 2021, 10:49 PM UTC

irrational wrote: post #28

rm999 wrote: post #23

>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 que…

> 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 Assuming you have constraints set up correctly (on delete no action or on delete restrict) then how could this ever happen? If you don’t have constraints set up correctly…

Assuming you're deleting the row because it shouldn't be used by read queries, constraints like you described prevent the problem of having orphaned records in the child table but also prevent you from achieving your goal. On delete cascade would allow you to achieve your goal and prevent the orphaned records but could lead to deleting more than intended (especially if the child table is also a parent table referenced by further foreign key constraints, its children could in turn have children, etc). Of course, with no action/restrict you could also manually cascade the delete, but if you actually don't want to delete a child row and there's not an appropriate alternative value for its foreign key then you're in a bit of a pickle.

So if you want to delete a user but keep the records of their orders and still know who made those orders, then some form of soft delete is probably your best option. I believe that's the point rm999 was making (in response to the article asserting that soft deletes are a "data model mistake"). Properly configured constraints can prevent an "oops" but don't really do anything to solve the problem of this sort of delete from some contexts but not others.

Re: Common data model mistakes made by startups

#47

Post by klowner » Sat, May 22, 2021, 11:14 PM UTC

ineedasername wrote: post #44

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?

I do this for personal projects, that seems like basic obvious stuff, IMHO.

Re: Common data model mistakes made by startups

#48

Post by feoren » Sat, May 22, 2021, 11:25 PM UTC

rm999 wrote: post #23

>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 que…

Secret hacker pro-tip: don't use foreign key constraints. At all. They're incompatible with the goals of most modern software systems. Ssh. It's a secret.

In before downvotes because your textbook/groupthink told you otherwise.

Re: Common data model mistakes made by startups

#49

Post by [deleted] » Sat, May 22, 2021, 11:26 PM UTC

FigmentEngine wrote: post #34

rm999 wrote: post #30

Earlier quoted context omitted.

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.

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)

[deleted]

Re: Common data model mistakes made by startups

#50

Post by feoren » Sat, May 22, 2021, 11:28 PM UTC

beachy wrote: post #31

pyrophane wrote: post #6

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.

At the other end of the scale is a data model designed for extreme extensibility. If you ever hear anyone bragging that their data model is entirely metadata driven, and can be used to model anything - without changing the database - that's a huge red flag, as is looking in and seeing tables called "element", "business object" and the like. Unfortunately, for most serious Enterprise systems, a degree of flexibility i…

Secret hacker pro-tip: inner platforms and key/value pair representations do not actually improve extensibility. You always have a schema -- you get to decide whether it's explicit or implicit. Their problem isn't that their data model is too extensible: it's that it's plain old bad.