RFC: Changes in projects and departments to support other mappings

[Chris Travers <..hidden..>]

Hi all;

I am looking at trying to create a generic system for mapping GL lines
to various ways of categorizing them.  My current thinking is that we
can get additional flexibility out of abstracting and unifying the
department and project code.  Additionally I have received a
requirement regarding Colombia that lines must be able to be attached
to customers or vendors.  This could be implemented in the same basic
model.

My thinking is something like:

CREATE TABLE line_category_class (
id serial not null unique,
label text primary key,
priority int not null default 0 -- order on screen, higher priority
items to the left
);

INSERT INTO line_category_class (id, label, priority) values (1,
'project', 50), (2, 'department', 40), (3, 'fund', '40'), (4, 'third
party', 10);

CREATE TABLE line_category (
id serial not null unique,
ref_key int,  -- this will have to be maintained through triggers
cat_class_id int not null references line_category_class(id),
label text not null,
primary key (label, cat_class_id),
unique(id, cat_class_id)
);

journal_line_category (
line_id int references journal_line(id)
line_category int not null;
line_class_id int references line_category_class(id)
foreign key (line_category, line_class_id) references
line_category(id, cat_class_id)
primatry key (line_id, line_class_id)
);

What do people think about this approach?

Best Wishes,
Chris Travers