Forum OpenACS Q&A: Re: Augmenting acs_objects - Add package_id, name, overview, etc. to it

Dirk, Harrison's book Oracle SQL High-Performance Tuning is indead excellent, but your criticism of the OpenACS object model based on page 489 of that book is simply absurd.

Harrison barely even touches on the subject of object modeling in an RDBMS. His whole section on "mapping entities or classes to tables" is barely 3 pages long, he never mentions any real systems, and the table you are referring to on page 489 is at best a simple back of the envelope take on the performance ramifications. It looks at three very generic and high-level options: "Single table", "separate subtype and supertype stables" (which OpenACS uses), and "separate subtype tables only - no supertype table". At best, the whole section is useful soley as a starting point for further thinking about performance implications - it offeres no actual answers, and it certainly doesn't even attempt to discuss any other software design considerations whatsoever.

And Harrison at least, doesn't seem to pretend that those 3 pages are anything more.

I think it's a reasonably thing to thing about moving something we would have for the majority of the type tables up into acs_objects. As a starting point, here are the object types and counts on select object_type, count(*) from acs_objects group by object_type having count(*) > 25;
       object_type      | count
*+ acs_message          |    38
 + acs_message_revision |    44
   acs_sc_msg_type      |    46
*  apm_package          |   140
*  apm_package_version  |    44
*  apm_parameter        |   128
*  apm_parameter_value  |   658
*+ bookmark             |   378
*+ bt_bug               |   254
*+ bt_patch             |    61
*  content_extlink      |   125
*  content_folder       |   117
*  content_item         |   476
   content_revision     |   168
   cr_item_child_rel    |    64
*+ etp_page_revision    |   341
*+ faq_q_and_a          |    50
*+ forums_message       | 32307
   membership_rel       |  6186
   notification         |  1486
   notification_request |  9832
*  site_node            |    97
*  static_page          |   188
*+ url                  |   504
*  user                 |  6186
   user_portrait_rel    |    31
I put a * next to those I think have a reasonable shot at having a name that is non stupid (i.e. user_portrait_rel could have a name but the name would simple be the ids in the table concatenated which seems like a bad thing to denormalize).

I also put a + by those things I think would be better off in the content repository (etp_page_revision and acs_message_revision are in fact in the CR already iirc).

In looking at the results I am left thinking that most things you actually would want to display to people with an attached name actually should really be in the content repository anyway and would have names there. I do want to see the "all new content sitewide", "all content by user X", and "all new content in subsite Y" be fast enough to use (which includes making permissions fast enough to do this -- something Don is working hard on as we type).