[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

Plan for refactoring user information

Hi all;

I am looking at reactoring user information in 1.3.  Here is my basic proposal:

1)  Users are connected to three other tables:  employee (for HR
information), preferences (for their own preferences), and locations
(for business location).

The basic user table looks like:
create table db_users (
 id serial primary key,
 username text unique not null,
 active bool default true not null,
 employee_id int references employee (id),
 location_id int references location (id)

On login, the application checks db_users (not users so we avoid
conflicts with setups with Pg-central in the main db) to see that the
user exists and is active.  If not, it aborts.

The employee table remains unchanged for now.

The preferneces table looks like:

create table preferences(
                     id integer primary key references db_users(id)
deferrable initially deferred,
                     countrycode text,
                     currency text,
                     dateformat text,
                     menuwidth text,
                     numberformat text,
                     printer text,
                     signature text,
                     stylesheet text,
                     templates text,
                     timeout numeric,
                     vclimit numeric

Compared to users_conf in 1.2, it is designed to store only
usable-changeable preferences.

The location table stores information on business locations.
Employees are connected locations for purposes like addresses:

       address1 text,
       address2 text,
       city text,
       state text,
       country text,
       zipcode text,
       companyname text

This would clearly separate user-modifiable data from non-modifiable
data, and would help to normalize the structure to a large extent.
Any feedback?

Best Wishes,
Chris Travers