Thread: The best table's scheme?

I have a such task:

1. table USERS
2. table DEPARTMENTS
 - where each DEPARTMENT needs to store LIST of USERS in it
3. table FIRMS
 - where each FIRM needs to store LIST of USERS in it
4. table EVENTS
 - and where each EVENT needs to store LIST of _MEMBERS_ - USERS or
DEPARTMENTS or FIRMS in it

The only way i see - is to create extra table like MEMBERS (USER, INTHE) and
to
store records about membership of each user in departments and firms. But I
don't know how about it's efficiency.

I thought about using arrays in PSQL to create FIRMS (.., users int4[]), but
I havent' found how to make queries like "SELECT * FROM users WHERE id IN
(SELECT USERS from FIRMS).

Can anyone help or advice me something?

Thanks for reading my message.
Sorry for taking your time.

---
Bob Zatolokin

Bob Zatolokin wrote:

> I have a such task:
>
> 1. table USERS
> 2. table DEPARTMENTS
>  - where each DEPARTMENT needs to store LIST of USERS in it
> 3. table FIRMS
>  - where each FIRM needs to store LIST of USERS in it
> 4. table EVENTS
>  - and where each EVENT needs to store LIST of _MEMBERS_ - USERS or
> DEPARTMENTS or FIRMS in it
>

It depends whether you have a many-to-one or many-to-many relationship. In the
first case each user is only ever a member of one department, in which case you
can store the dept id in the user table. If each user can be a member of
several firms (moonlighting!), then you do need a separate table to store the
many-to-many relation. This is very efficent if you define indexes on all the
joining fields. I don't recommend going near arrays - I regretted it later &
haven't touched them for years. So a solution might look like:

create table firm_user (
  firm_id int4,
  user_id int4);

select * from user, firm, firm_user where user.id = firm_user.user_id and
firm.id = firm_user.firm_id

If I don't make sense, look for some documentation on database design,
normalisation & normal-forms,

Ivan