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_part
s, 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
Last activity: Sep 16, 2021, 11:10 PM