4 ms·
OK, that's definitely a matter for a custom trigger on the table. Another option would be to keep the chapter order in an array column on the Book table, since
by empthought 15y ago
OK, that's definitely a matter for a custom trigger on the table.
Another option would be to keep the chapter order in an array column on the Book table, since arguably the arrangement of chapters belongs to the book rather than the chapter.
PostgreSQL array documentation is here: http://www.postgresql.org/docs/9.1/static/arrays.html http://www.postgresql.org/docs/9.1/static/arrays.html
- famousactress 15y agoarguably the arrangement of chapters belongs to the book rather than the chapter That's a really excellent point, and array columns look like a really cool solution! I suppose the only bummer is that it introduces a way to get things out of sync (I could delete a Chapter row, but forget to remove it from the array in Book), but in general this is a much better approach than the status-quo solution.
- empthought 15y agoI think you should be able to write a delete rule on Chapter that will ensure that no array in Book contains that chapter, whether through cascading the delete or through raising an error.
- ams6110 15y agoIf you're ever thinking of writing a trigger for any purpose, think again. And again. They are used improperly far more often than not. Things should not happen as "magic" side effects in a database, which is what triggers do. Generally better to be explicit with code in a function or stored procedure.
- empthought 15y agoIn general I disagree, but in this specific case you are right that a procedural interface to manipulating books/chapters is preferable. Essentially if your API is single-table DML, then triggers are preferable. If multiple tables need to be manipulated at the same time to complete the full operation, then there should be a procedural API and triggers are likely unnecessary.