Hacker News Reader

Comment by hueving | Hacker News Reader

>(1) Sally deletes an employee record >(2) Bob deletes that employee's department

I would argue that a good design would actually prevent you from deleting important records like these. i.e. when Bob clicks delete, it just marks the object as deleted and it stops showing up in reports/interfaces. Then maybe you have something clean it up 90 days later or something.

View on HN

Yes. This is pattern I've seen in both companies I worked for, and it makes so much sense.

Similairly you don't make table ARTICLES with column AMOUNT and KIND. You make table CHANGES with DIFFERENCE and KIND, and only ever add rows to that table (possibly with negative DIFFERENCE).

Sorry I don’t really understand what you’re trying to say here. Could you expand on the differences in data between the two tables, please?

In the first case, you only know the AMOUNT. You can't undo transactions because you only have 1 object stored, the total amount. If you store DIFFERENCE, then you'll have a table of every transaction and can undo them individually. The cost is having to sum the table whenever you want to know the amount (which is usually worth the flexibility).

Regarding the performance - you can add triggers writing to additional table with a sum, or maintain that table in the code that adds rows, or calculate it each time you want to know the sum.

This is what I understood (s)he means

Scenario: Laptop's price is initially $800 and then increases to $850 before dropping to $825

Design 1: with columns Amount and Kind will just have a single row which will get modified with the latest value

Design 2: Will have 3 rows in the above case

Difference | Kind | Time stamp

800 | 'Laptop' | ts 1

+50 | 'Laptop' | ts 2

-25 | 'Laptop' | ts 3

So this latter design has 'undo' builtin

edit: formating

Yes, that's what I've meant. This also gives you easy accountability (rows can have information like user_id, ip, timestamp).

Reply on news.ycombinator.com