I know I am just ranting here, but the group part of the data model is way messed up. I started to use this part of the data model to setup a new set of groups. The group type was called 'customer'. Then I added a group of this type, say 'Customer1'. As expected, users were supposed to be able to be assigned to multiple customer groups, then when a user logs in, they might choose what customer group they wanted to be in to place an order, etc.
It turns out not to be all that easy to come up with a list of customer groups that a user belongs to! Actually it requires a join of the group_types table:
create table group_types ( group_type varchar(100) not null constraint group_types_pk primary key constraint group_types_obj_type_fk references acs_object_types (object_type), default_join_policy varchar(30) default 'open' not null constraint group_types_join_policy_ck check (default_join_policy in ('open', 'needs approval', 'closed')) );
With the acs_objects table, on the object_type column, which does not even appear to have an index. Joining with the acs_objects table is not something you are supposed to do, even with the object_id column! Of course then you have to throw in joins with the group_member_map view and the groups table.
It seems to me that group_type should be an object referencing the acs_objects table with object_type = group_type, and the groups table should have a column for group_type_id, referencing this table.
Okay, so group types are defined in the group_type table. But look at this:
create table group_type_rels ( group_rel_type_id integer constraint gtr_group_rel_type_id_pk primary key, rel_type varchar(100) not null constraint gtr_rel_type_fk references acs_rel_types (rel_type) on delete cascade, group_type varchar(100) not null constraint gtr_group_type_fk references acs_object_types (object_type) on delete cascade, constraint gtr_group_rel_types_un unique (group_type, rel_type) );
So this table bypasses the group_types table completely! Good news is that it is Tuesday.