Re: The best table's scheme?

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