Forum OpenACS Development: groups and group_types massively messed up.

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.

Collapse
Posted by Stephen . on
acs_objects.object_type is indexed, and I think it's fine to join against the acs_objects table. If we're not allowed to touch it then what's the point?
comment on table group_type_rels is '
  Stores the default relationship types available for use by groups of
  a given type. We May want to generalize this table to object_types and
  put it in the relationships sql file, though there is no need to do so
  right now.
';
Maybe this got half finnished?

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.
Why?
Collapse
Posted by Tom Jackson on

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."

Collapse
Posted by Stephen . on
Why isn't there a group_type column in the groups table?
Because that would be a denormalisation. Why isn't there object type (group type) information in any of the object tables? It's in acs_objects.
The group_types.group_type column references the acs_objects.object_type column...
It references the acs_object_types.object_type column.

Can you get a list of customer groups the user belongs to by:

select
  m.group_id
from
  group_approved_member_map m,
  acs_objects o
where
  m.member_id = :customer_id
  and o.object_type = :customer_group_type
  and o.object_id = m.group_id
Collapse
Posted by Tom Jackson on

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