clinton has asked for the wisdom of the Perl Monks concerning the following question:
Take two objects: user and group. They have certain properties in common (eg object ID, parent ID, creation date, status, last modified date), and certain properties unique to each object (eg Group : name, description, User : firstname, surname, email address). So it made sense to have the following:
Table:User Table:Object Table:Group ---------- ------------ ----------- object_id <==> object_id <==> object_id email object_type name firstname parent_ID description surname created last_modified
But then we have an object (announcement) which can be subclassed, so there could be an announcement which is a death_notice, and an announcement which is a wedding.
A death notice only requires one firstname/surname, while a wedding has two. Also a death notice might have different fields (eg charities (as in 'please donate to...') while a wedding may have gift_lists.
Also, while you know that you want to search for a user or a group, you may not know whether you should be searching for an announcement under death notices, in_memoriam, obituaries etc. So there is significant overlap between these object sub-types.
So what are the approaches:
Table:Death Table:Wedding ----------- ------------- object_id object_id firstname firstname_1 surname surname_1 short_obit firstname_2 obituary surname_2 flowers message_from_couple charities details date_of_death date_of_wedding
Table:Announcement Table:Death Table:Wedding ------------------ ----------- ------------- object_id object_id object_id firstname short_obit firstname_2 surname obituary surname_2 flowers message_from_couple charitites details date_of_death date_of_wedding
...plus syntactic sugar in the classes to map field_1 to flowers, and date_1 to date_of_death.Table:Announcement ------------------ object_id notice_type firstname_1 surname_2 firstname_2 surname_2 summary details date_1 field_1 field_2 field_3
This has the advantage of easy searching across these objects, plus simplified storage and only a single join between Table:Object and Table:Announcement, but at the expense of redundant fields.
This has the advantage of being completely extensible, easy to search across objects, but is slower to construct each object (although I'd cache the constructed objects) and multi-field searches would involve many more table joins. Also, it is more difficult to conceptualise and is a pain to work with. And I have a feeling that it would scale poorly with the growing number of properties.Table:Object Table:Properties Table:Object_Properties ------------ ---------------- ----------------------- object_id property_id object_id object_type property_name (eg flowers) property_id ... property_type (eg text) value ... (or) text_value blob_value date_value boolean_value etc
I would appreciate any feedback from the experiences that you've had. I realise that this has nothing to do with Perl, but it is a design issue that most of us have had to deal with at one time or another.
many thanks
Clint
|
|---|