good style? - Mailing list pgsql-sql

hi,

I have 8 tables and this query:

select u.users_id, m.name as mandant_name, u.login_name, u.password, 
u.first_name, u.last_name, u.creation_date, g.name as groups_name, 
ae.acl_entry_id, a.name as acl_name, p.name as permission_name from mandant m, users_2_groups u2g, groups g, users u,
permissionp, 
 
acl a, acl_entry ae, groups_2_acl_entry g2ae where m.mandant_id = u.mandant_id and            u2g.groups_id =
g.groups_idand            u2g.users_id = u.users_id and            g2ae.groups_id = g.groups_id and
g2ae.acl_entry_id= ae.acl_entry_id and            ae.acl_id = a.acl_id and            ae.permission_id =
p.permission_id

I'm not using JOIN for get this information. would be JOIN a better sql 
programming style? faster?


Best Regards,
Rafal


sql script:

DROP TABLE groups_2_acl_entry;
DROP TABLE users_2_groups;
DROP TABLE groups;
DROP TABLE users;
DROP TABLE acl_entry;
DROP TABLE permission;
DROP TABLE acl;
DROP TABLE language;
DROP TABLE mandant;
DROP TABLE license;
DROP TABLE firm;



CREATE TABLE firm ( firm_id numeric(20, 0) NOT NULL, name varchar(40) NOT NULL, CONSTRAINT firm_pkey PRIMARY KEY
(firm_id)
) WITH OIDS;

INSERT INTO firm VALUES (1, 'polonium');



CREATE TABLE license ( license_id numeric(20, 0) NOT NULL, key varchar(100) NOT NULL, creation_date timestamp NOT NULL,
valid_fromtimestamp NOT NULL, expired timestamp, CONSTRAINT license_pkey PRIMARY KEY (license_id)
 
) WITH OIDS;

INSERT INTO license VALUES (1, 'NOT AT THIS TIME - SHOULD BE GENERATED', 
now(), now(), NULL);



CREATE TABLE mandant ( mandant_id numeric(20, 0) NOT NULL, firm_id numeric(20, 0) NOT NULL, license_id numeric(20, 0)
NOTNULL, parent_id numeric(20, 0), name varchar(20) NOT NULL, creation_date timestamp NOT NULL, CONSTRAINT mandant_pkey
PRIMARYKEY (mandant_id), CONSTRAINT fk_firm FOREIGN KEY (firm_id) REFERENCES firm (firm_id), CONSTRAINT fk_license
FOREIGNKEY (license_id) REFERENCES license 
 
(license_id), CONSTRAINT fk_parent FOREIGN KEY (parent_id) REFERENCES mandant 
(mandant_id)
) WITH OIDS;

INSERT INTO mandant VALUES (1, 1, 1, NULL, 'polonium', now());



CREATE TABLE language ( language_id int2 NOT NULL, lang_short char(2) NOT NULL, lang_long varchar(20) NOT NULL,
CONSTRAINTlanguage_pkey PRIMARY KEY (language_id)
 
) WITH OIDS;

CREATE UNIQUE INDEX language_lang_short_idx ON language (lang_short);
CREATE UNIQUE INDEX language_lang_idx ON language (lang_short, lang_long);

INSERT INTO language VALUES (1, 'de', 'deutsch');
INSERT INTO language VALUES (2, 'en', 'english');



CREATE TABLE acl ( acl_id int2 NOT NULL, name varchar(20) NOT NULL, description varchar(200), CONSTRAINT acl_pkey
PRIMARYKEY (acl_id)
 
) WITH OIDS;

CREATE UNIQUE INDEX acl_name_idx ON acl (name);

INSERT INTO acl VALUES (1, 'mmcms.access', 'acl for login module');
INSERT INTO acl VALUES (2, 'mmcms.system', 'acl for system module');
INSERT INTO acl VALUES (3, 'mmcms.admin', 'acl for admin module');
INSERT INTO acl VALUES (4, 'mmcms.category', 'acl for category module');
INSERT INTO acl VALUES (5, 'mmcms.context', 'acl for context module');



CREATE TABLE permission ( permission_id int2 NOT NULL, name varchar(20) NOT NULL, description varchar(200), CONSTRAINT
permission_pkeyPRIMARY KEY (permission_id)
 
) WITH OIDS;

CREATE UNIQUE INDEX permission_name_idx ON permission (name);

INSERT INTO permission VALUES (1, 'access', 'access permission');
INSERT INTO permission VALUES (2, 'read', 'read permission');
INSERT INTO permission VALUES (3, 'write', 'write permission');
INSERT INTO permission VALUES (4, 'execute', 'execute permission');
INSERT INTO permission VALUES (5, 'modify', 'modify permission');
INSERT INTO permission VALUES (6, 'list', 'list permission');



CREATE TABLE acl_entry ( acl_entry_id int2 NOT NULL, acl_id int2 NOT NULL, permission_id int2 NOT NULL, CONSTRAINT
acl_entry_pkeyPRIMARY KEY (acl_entry_id), CONSTRAINT fk_acl FOREIGN KEY (acl_id) REFERENCES acl (acl_id), CONSTRAINT
fk_permissionFOREIGN KEY (permission_id) REFERENCES 
 
permission (permission_id)
) WITH OIDS;

-- acl_entry for mmcms.access acl with access permission

-- acl 'mmcms.access' has 'access' permission
INSERT INTO acl_entry VALUES (1, 1, 1);
-- acl 'mmcms.system' has 'read' permission
INSERT INTO acl_entry VALUES (2, 2, 2);
-- acl 'mmcms.system' has 'write' permission
INSERT INTO acl_entry VALUES (3, 2, 3);
-- acl 'mmcms.admin' has 'read' permission
INSERT INTO acl_entry VALUES (4, 3, 2);
-- acl 'mmcms.admin' has 'write' permission
INSERT INTO acl_entry VALUES (5, 3, 3);



CREATE TABLE users ( users_id numeric(20, 0) NOT NULL, mandant_id numeric(20, 0) NOT NULL, language_id int2 NOT NULL,
login_namevarchar(50) NOT NULL, password varchar(15) NOT NULL, first_name varchar(20) NOT NULL, last_name varchar(20)
NOTNULL, creation_date timestamp NOT NULL, last_login_date timestamp, status int2 NOT NULL, CONSTRAINT users_pkey
PRIMARYKEY (users_id), CONSTRAINT fk_mandant FOREIGN KEY (mandant_id) REFERENCES mandant 
 
(mandant_id), CONSTRAINT fk_language FOREIGN KEY (language_id) REFERENCES language 
(language_id)
) WITH OIDS;

CREATE UNIQUE INDEX users_login_name_idx ON users (login_name);

INSERT INTO users VALUES (1, 1, 1, 'rafcio@polonium.de', 'test', 
'Rafal', 'Kedziorski', now(), NULL, 0);



CREATE TABLE groups ( groups_id numeric(20, 0) NOT NULL, mandant_id numeric(20, 0) NOT NULL, name varchar(20) NOT NULL,
descriptionvarchar(200) NOT NULL, creation_date timestamp NOT NULL, CONSTRAINT groups_pkey PRIMARY KEY (groups_id),
CONSTRAINTfk_mandant FOREIGN KEY (mandant_id) REFERENCES mandant 
 
(mandant_id)
) WITH OIDS;

CREATE UNIQUE INDEX groups_name_idx ON groups (mandant_id, name);

-- every mandant should have own access group
INSERT INTO groups VALUES (1, 1, 'access', 'access group', now());
INSERT INTO groups VALUES (2, 1, 'system', 'system group', now());
INSERT INTO groups VALUES (3, 1, 'admin', 'admin group', now());



CREATE TABLE users_2_groups ( users_2_groups_id numeric(20, 0) NOT NULL, users_id numeric(20, 0) NOT NULL, groups_id
numeric(20,0) NOT NULL, valid_from timestamp NOT NULL, expired timestamp, CONSTRAINT users_2_groups_pkey PRIMARY KEY
(users_2_groups_id),CONSTRAINT fk_groups FOREIGN KEY (groups_id) REFERENCES groups 
 
(groups_id), CONSTRAINT fk_users FOREIGN KEY (users_id) REFERENCES users (users_id)
) WITH OIDS;

CREATE UNIQUE INDEX users_2_groups_usersgroups__idx ON users_2_groups 
(users_id, groups_id);

INSERT INTO users_2_groups VALUES (1, 1, 1, now(), NULL);
INSERT INTO users_2_groups VALUES (2, 1, 2, now(), NULL);
INSERT INTO users_2_groups VALUES (3, 1, 3, now(), NULL);



CREATE TABLE groups_2_acl_entry ( groups_2_acl_entry_id numeric(20, 0) NOT NULL, groups_id numeric(20, 0) NOT NULL,
acl_entry_idint2 NOT NULL, CONSTRAINT groups_2_acl_entry_pkey PRIMARY KEY (groups_2_acl_entry_id), CONSTRAINT fk_groups
FOREIGNKEY (groups_id) REFERENCES groups 
 
(groups_id), CONSTRAINT fk_acl_entry FOREIGN KEY (acl_entry_id) REFERENCES 
acl_entry (acl_entry_id)
) WITH OIDS;

INSERT INTO groups_2_acl_entry VALUES (1, 1, 1);
INSERT INTO groups_2_acl_entry VALUES (2, 2, 2);
INSERT INTO groups_2_acl_entry VALUES (3, 2, 3);
INSERT INTO groups_2_acl_entry VALUES (4, 3, 4);
INSERT INTO groups_2_acl_entry VALUES (5, 3, 5);