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