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.

From what I can tell, groups can only have one group_type. Why isn't there a group_type column in the groups table? The group_types.group_type column references the acs_objects.object_type column, but there are many, I would say most objects are not group types. If the group_types table had an integer primary key column referencing acs_objects.object_id, with an object_type of group_type, it would be obvious and easy to extract this information. The number of columns in the group_types table would be the same, and the join column would be an integer.

I should ask why it is setup the way it currently is, there may be a good reason, and I would love to discover what it is.

Joining with the acs_objects table could be alright, but the join required to extract the information I want seems to require joining with several additional views. Maybe I should just ask: is this good? It might be. This reminds me more of a joke about a guy going into his bank to get some of his money. The banker said "You can't have any of you money, because you have what we call an 'You can't have any of your money' account."

Ah, yes I am starting to see the light, thanks. This seems to work:

select 
 m.group_id,
 g.group_name
from
 group_approved_member_map m,
 groups g,
 acs_objects o
where
 m.member_id = :customer_id
and o.object_type = :customer_group_type
and o.object_id = m.group_id
and m.group_id = g.group_id