3 ms·
The tradeoff here is that you lose the foreign key constraint, correct? So if you delete a tag, there is no way for the database to automatically remove all ref
by RussianCow 4y ago
The tradeoff here is that you lose the foreign key constraint, correct? So if you delete a tag, there is no way for the database to automatically remove all references to it. Or is there some way to do this now?
- brianwawok 4y agoRight . More like nosql FKs. How high is the business risk if you have a random tag with no name? Skip it’s display jn the UI
- deleted 4y ago[deleted]
- a1369209993 4y ago> So if you delete a tag, there is no way for the database to automatically remove all references to it. I'm not sure about implementation/support in Postgres specifically, but in the general case of a column of tag bitfields, the database could easily maintain a global popcount (ie, "number of rows with this tag") and soft-delete flag for each tag, and clear any soft-deleted tags on (possibly-only-write-)access. When a soft-deleted tag reaches popcount == zero, it counts as garbage collected and can be reused for a new tag.
- dahdum 4y agoYes, but that’s easily handled with a trigger. My first implementation actually had a regular join table of items_tags which used a trigger to update the items.tags intarray. Wasn’t super performant but let us use our existing templates for 1-many to implement the UI. Nowadays you can just use a tagging component with integrated search for the UI.
- ohgodplsno 4y agoBut then again, the reasons for _deleting_ a tag are very low. What really happens is that you want your searches on that tag to just return nothing, and that's more of an application level responsibility. Your #absolutely_vile_tag_that_would_get_you_in_jail just enters a blacklist, and you're done.