The better of two table designs?

[2trax]

Hi all,

I am trying to mimic the unix security model (users and groups) in a web
app.

I have thought of two ways of implementing this, but am unsure which would
be better. The first design utilises three tables, while the second
utilises two tables, however one of the columns in the first table is an
an array.

Design 1:

CREATE TABLE users (
user_name varchar(31) UNIQUE NOT NULL,
enc_password varchar(63) NOT NULL;
);

CREATE TABLE groups (
group_id serial PRIMARY KEY,
group_name varchar(31) UNIQUE NOT NULL,
group_description text;
);

CREATE TABLE users_groups_union (
user_name varchar(31) REFERENCES users ON DELETE CASCADE NOT NULL,
group_id integer REFERENCES groups ON DELETE RESTRICT NOT NULL;

);
Design 2:

CREATE TABLE users (
user_name varchar(31) UNIQUE NOT NULL,
enc_password varchar(63) NOT NULL,
user_groups integer[] REFERENCES groups NOT NULL ON DELETE RESTRICT;
);

CREATE TABLE groups (
group_id serial PRIMARY KEY,
group_name varchar(31) UNIQUE NOT NULL,
group_description text;
);
So, my question is, which design will be better in the long run? I've used
a design similar to (1) before, but I have never tried holding arrays in a
column as in (2).... does this present any consistency issues? For
example, if a group is deleted, will pg check through all the values in
all the arrays to check that group_id was not in use?

Using design (1) I have to query two tables to check the password and
obtain the groups that the user belongs to, while with design (2) this can
be done querying just the users table. Presumably this has performance
benefits?

Thanks for any feedback,

Sam.
---
Posted via news://freenews.netfront.net
Complaints to ne**@netfront.net