Hacker News new | past | comments | ask | show | jobs | submit login

You made a great example, and I think it's the most important thing to remember when designing schema. I've seen arguments for/against 1NF, 3NF, various db features, etc, but really, the schema should model the real world relationships.

If your InvoiceItem has no description, and only has a FK to Product to get the description you're going to have a bad time. Once you build a system around a wrong relationship like that, it's very hard to go back and fix the inevitable issue that updating a product changes old sales invoice records.

Never done something regarding invoices and related stuff, so just an academic question: Why not just version the product description and FK to appropriate version?

That's one solution. You could copy the description to the invoice item. That's another. There are a lot more solutions, but the point is none of them will be trivial to implement in a large existing code base. It's really important to get the data model right as much as possible up front. This is a bit counter to all of the agile/scrum rage of iterate and refactor. That works for code, but schemas and data migrations do not lend themselves to constant refactoring.

versioning solves this problem.

in practice, you have to support retroactive changes, so you have to have "middle" values for versions (i.e. 1.1 between 1 and 2).

People are usually interested in the timestamps associated with those versions (for which period of time is this version valid, when was this version created), so in practice it's easier to just keep track of the timestamps, and leave the versions implicit.

Applications are open for YC Summer 2020

Guidelines | FAQ | Support | API | Security | Lists | Bookmarklet | Legal | Apply to YC | Contact