I need an organization hierarchy package for use with the stores component of the subsite-enabled e-commerce package. I'll also use it on this project to generate About Us and Contact Us pages. Below is the first draft of the data model based on my requirements, and please let me know if you have any input on how this package should be designed for use with OpenACS.
create table org2_organization (
organization_id int primary key references groups(group_id),
legal_name text not null unique,
informal_name text not null unique,
description text,
tagline text,
-- email stored in parties table
package_id int references apm_packages(package_id)
);
-- do we care about divisions?
create table org2_location (
location_id int primary key references groups(group_id),
organization_id int not null references org2_organization(organization_id),
location_name text not null,
description text,
address1 text,
address2 text,
city text,
state char(2) references us_states(abbrev),
postal_code text,
country char(2) references countries(iso),
landmark_description text, -- e.g. Located near... (html)
phone_primary text,
phone_tollfree text,
phone_fax text,
-- email stored in parties table
timezone text, -- e.g. -600 (CST)
primary_p bool not null, -- primary location?
sort_key numeric not null default 10,
package_id int not null references apm_packages(package_id)
);
create sequence org2_location_hours_id_seq start 2000;
create table org2_location_hours (
location_hours_id int primary key,
-- day values 1-7 (easier to sort)
day_begin int not null check (day_begin >= 1 and day_begin <= 7),
day_end int not null check (day_end >= 1 and day_end <= 7),
time_open timestamptz not null,
time_close timestamptz not null,
location_id int not null references org2_organization_location(location_id),
package_id int not null references apm_packages(package_id),
unique (day_begin,location_id),
unique (day_end,location_id)
);
create table org2_department (
-- departments at large companies may have specific hours
department_id int primary key references groups(group_id),
location_id int references org2_location(location_id),
department_name text not null, -- e.g. Sales, Parts, Service
description text,
phone_primary text,
phone_fax text,
-- email stored in parties table
parent_id int references org2_department(department_id),
package_id int not null references apm_packages(package_id),
unique (department_name,location_id)
);
-- do we care about teams?
create table org2_position (
position_id int primary key references groups(group_id),
department_id int references org2_department(department_id)
job_title text not null, -- e.g. Parts Manager
description text,
package_id int not null references apm_packages(package_id),
unique (job_title,department_id)
);
create table org2_employee (
employee_id int primary key references persons(person_id),
position_id int references org2_position(position_id),
title text not null, -- e.g. Mr., Mrs., Dr.
-- first_name, last_name stored in persons table
-- email stored in parties table
-- if they have an email address, should we make them a user?
bio_short text, --html
bio_long text, --html
phone_primary text,
phone_fax text,
supervisor_id int references org2_organization_employee(employee_id),
package_id int not null references apm_packages(package_id)
);
create table org2_employee_photo (
employee_id int references org2_employee(employee_id),
image_id int references images(image_id),
primary key (employee_id,image_id)
);
