PostgreSQL best way to model position within scope of foreign key-CodePudding

I have boxes that contain books. The books table has a foreign key called "box_id", referencing the "id" of the box to which it belongs. I need a way to keep track of the relative position of each book within its box. A book can be removed from its box regardless of its position inside the box, but when a book is put into a box, it always goes in last. A book can be moved from one box to another. The order of books inside a box cannot be changed. What's the best way to model this? I prefer not to have a third table.

CodePudding user response：

Put a sort_order column in the books table and use that in an ORDER BY clause when you query the books in a box.

You would update the sort_order every time you move a book from one box to another (or, put it in a box the first time) with a value larger than the other sort_order values in that box - or, for simplicity, with a value larger than all sort_order values in the entire table. For that, you can use a sequence or the current_time() (assuming you don't move multiple books into the same box in the same transaction). The update can be done either by the client code or using an ON INSERT/UPDATE trigger.

CodePudding user response：

The normalized way would be to create a third table including box_id, book_id and book_position_in_box columns.

A denormalized way can be to create a book_id_array column in table box because the array types keep the order of the items in the arrays.

Then you can automatically update this array type column by trigger functions ON INSERT, ON UPDATE, ON DELETE defined on table books, like :

CREATE FUNCTION box_insert_update() RETURNS TRIGGER LANGUAGE plpgsql AS
$$
BEGIN
  UPDATE box
     SET book_id_array = book_id_array || array[NEW.id] --  book added at the last place in the NEW box
   WHERE id = NEW.box_id ;
  RETURN NEW ;
END ;
$$ ;

CREATE TRIGGER box_insert_update BEFORE INSERT OR UPDATE ON book
FOR EACH ROW EXECUTE FUNCTION box_insert_update() ;

CREATE FUNCTION box_update_delete() RETURNS TRIGGER LANGUAGE plpgsql AS
$$
BEGIN
  UPDATE box
     SET book_id_array = array_remove(book_id_array,OLD.id) -- book is removed from the OLD box with no gap
   WHERE id = OLD.box_id ;
  RETURN OLD ;
END ;
$$ ;

CREATE TRIGGER box_update_delete AFTER UPDATE OR DELETE ON book
FOR EACH ROW EXECUTE FUNCTION box_update_delete() ;

Full test sample & result in dbfiddle.