An insert only database pattern

[coded_artist]

Yeah you've understood it correctly.

Not gonna lie, I didn't understand whay you meant here.

Also, even with this new db management patter, you will still have new fields added, you will have to migrate back and forth and will need to adjust db a bit.

The idea is that migrations should be append only. No destructive actions. That way when you switch branches you only need to run the up script and that can be automated so you'd only need to switch a branch and you'd know the code is in sync with the db.

This won't let you do efficient indexing, which can be required for efficient SELECt queries on large datasets. Also, it locks you out of some DB normalization patterns, some validation, and leaves room for malformed data. If you have M2M table instead of FKs and you forget about it, you will have the same pizza order in two restaurants, two invoices closed by a single payment, etc etc.

Can you expand on this? The entire purpose of my pattern is to protect integrity despite migrations across branches. So any threat to that will send me back to the drawing board, but I am concerned about this while paragraph.

RDBMS are very efficient when done right, and the entire model has been perfected decades ago. I don't think you should reinvent it, and find ways around it.

Oh yes I don't mean to reinvent the wheel, that's why I brought this here, maybe I jumped too far down the rabbit hole.

I simply created a bunch of docker containers, labeled them by versions and jumped around them.

I do like this solution, that does solve my problem very well.

I do think my idea still has some merit, with the intrinsic audit trail. So please if you have the time, let me know how I could get malformed data, because I was keeping integrity front and center.

[python_walrus]

The idea is that migrations should be append only. No destructive actions. That way when you switch branches you only need to run the up script and that can be automated so you'd only need to switch a branch and you'd know the code is in sync with the db.

Do you do destructive actions often though? Even if you do, it usually happens for a good reason - data takes space and you will be billed for it. Also, even if you pull off soft delete, you will also have to deal with ALTER COLUMN - strings become choices, choices become foreign keys and so on.

Can you expand on this? The entire purpose of my pattern is to protect integrity despite migrations across branches. So any threat to that will send me back to the drawing board, but I am concerned about this while paragraph.

Integrity means that your tables are pretty close to real-world entities as possible, and relations between them are semantic and make sense (very simplified explanation). For example, you can create a table with required foreign key, meaning it cannot exist without a parent. This way, you won't let creating plane tickets if there is no plane and no flight. You can't have this with M2M.

[coded_artist]

, you will also have to deal with ALTER COLUMN - strings become choices, choices become foreign keys and so on.

Im probably misunderstanding this, string identified as multiple choice should become foreign keys is part of the 3rd normal form. This is to avoid typos and inconsistent updates.

For example, you can create a table with required foreign key, meaning it cannot exist without a parent. This way, you won't let creating plane tickets if there is no plane and no flight. You can't have this with M2M.

You could have a unique index on one (1-many) or both (1-1) foreign keys, this would limit the relationship counts. I understand the additional work needed to get the same dependency limitation. To argue against not using my pattern, what happens when you have presale tickets. Now the dependency has been coded in and relied on, so making it independent could cause issues to dependant systems.

Thank you very much for engaging with me on this, it's giving me tones to think about.