467,130 Members | 1,263 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

Post your question to a community of 467,130 developers. It's quick & easy.

The better of two table designs?

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
Nov 11 '05 #1
  • viewed: 1155
Share:

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

15 posts views Thread by Holly | last post: by
8 posts views Thread by Zero.NULL | last post: by
8 posts views Thread by Rob Mazur | last post: by
1 post views Thread by elfyn@ibabyshop.co.uk | last post: by
reply views Thread by Steve Walker | last post: by
39 posts views Thread by windandwaves | last post: by
reply views Thread by service0073@watchec.com | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.