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.