Sample Header Ad - 728x90

Non-overlapping rectangles constrained to a boundary

5 votes
1 answer
559 views
I am trying to model placement of parts on a circuit board. Without any meaningful constraints, my basic schema looks like this: create table part ( part_id bigserial primary key, name text not null, width double precision not null, height double precision not null ); create table board ( board_id bigserial primary key, width double precision not null, height double precision not null ); create table board_part ( board_id bigint not null references board, part_id bigint not null references part, position point not null ); ([SQL Fiddle](http://sqlfiddle.com/#!15/1cfb7/5) , [Visualization](https://i.sstatic.net/89w7d.jpg)) For b and b2 any board_parts, I want to enforce the following constraints: 1. b lies on the board: box(b.position, point(b.part.width,b.part.height)) <@ box(point(0,0), point(b.board.width,b.board.height)) 2. b and b2 do not overlap if they lie on the same board: b.board_id != b2.board_id or not (box(b.position, point(b.part.width,b.part.height)) && box(b2.position, point(b2.part.width,b2.part.height))) **How can I achieve this (without too much data duplication)?** Changing the schema is fine. Here is [my best attempt (SQL Fiddle)](http://sqlfiddle.com/#!15/5773c/2) , taking inspiration from Erwin's [answer to my previous question](https://dba.stackexchange.com/a/58972/34321) . It enforces the constraints I wanted, but has a lot of duplicate data in the board_part table. I imagine I could write a function to fill in the board_width, board_height, part_width, and part_height fields automatically, but it still feels wrong having so much duplicate data around. Also, keying to the width/height fields feels like a hack.
Asked by Snowball (327 rep)
Feb 22, 2014, 06:05 AM
Last activity: Sep 16, 2021, 11:10 PM