Re: PG Admin

On Dec 5, 2006, at 13:08 , Bob Pawley wrote:

> The physical devices don't get numbered until the design is
> established and stable. This is known as the construction stage.

I guess I would set up a couple of tables to track this ordering
independently of the devices themselves. Rough schema:

create table devices
(
device_id serial primary key
device_name text not null unique
);

create table plans
(
plan_id serial primary key
, plan_name text not null unique
);

create table plan_devices
(
plan_id integer not null
references plans
, device_id integer not null
references devices
, device_order serial not null
, unique (plan_id, device_id)
, unique (plan_id, device_order)
);

This idea is based around the idea that every time you make a change
to the plan, it's in essence a new plan. You insert a new plan in
plans, reset the plan_devices_device_order_seq (created by the
device_order serial column), and insert the devices for the new plan
into plan_devices in the order they should be. Of course, sequences
aren't transaction safe, but unless others are pulling from the
sequence while the new devices are being assigned to the plan, it
should be safe. You can also check the integrity of the device_order
column after the insert to make sure it's gapless.

Michael Glaesemann
grzm seespotcode net