I have a table (see below for ddl) which implements a set of locations
for an organization, from the whole organization to shelves in a
particular room. Users never see the primary key, only the NAMEX,
usually as a drop down selection list, sometimes a single selection
other times a multiselection. Users, being users, want the ability to
change names and to see the names in sorted order at each level. If I
didn't have to have changeable names, I could define the data in
alphabetic order. Given that I can't do that, is there (relatively) easy
way to present the data in a sorted manner? This is the same as seeing
you directory tree in Windows explorer with the name chosen as the sort
key. The root directory is sorted in filename order, then every
directory under in it also in name order, down an arbitrary number of
levels. In my examples, users attach people, functions, things etc. to
locations at any level, can add additional locations at any level
(dividing up a room, adding shelving, buying a new building, etc.).
Because history is important, locations can only become inactive, never
deleted. Because of the arbitrary number of levels, I can not join any
other table to achieve the sort I want.
CREATE TABLE IS3.LOCATIONS (
LOC_ID INTEGER GENERATED BY DEFAULT AS IDENTITY
(START WITH 1, INCREMENT BY 1, CACHE 20) NOT NULL,
NAMEX VARCHAR(30) NOT NULL,
LEFTX INTEGER NOT NULL,
RIGHTX INTEGER NOT NULL,
ENTITY_ID INTEGER NOT NULL,
INACTIVE CHARACTER(25),
PRIMARY KEY(LOC_ID)
)
GO
ALTER TABLE IS3.LOCATIONS
ADD CONSTRAINT INACTIVE
CHECK (INACTIVE in ('Y','N'))
GO
ALTER TABLE IS3.LOCATIONS
ADD CONSTRAINT SQL040717191150980
FOREIGN KEY(ENTITY_ID)
REFERENCES IS3.ENTITIES_PUB(ENTITY_ID)
ON DELETE NO ACTION
ON UPDATE NO ACTION
GO
Received on Thu Sep 29 2005 - 10:31:00 CDT
