I'm trying to figure out the best way to map my objects to the database. I started with this...

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

[download]

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:

1) Table per announcement type:

    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

[download]

2) One announcements table plus separate tables for extra attributes:

    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

[download]

3) One table with redundant columns:

    Table:Announcement
    ------------------
    object_id
    notice_type
    firstname_1
    surname_2
    firstname_2
    surname_2
    summary
    details
    date_1
    field_1
    field_2
    field_3

[download]

...plus syntactic sugar in the classes to map field_1 to flowers, and date_1 to date_of_death.

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.

4) One table for all property values

By this, I mean a completely different style of storage where you have these tables: 

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

[download]

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.

I am leaning towards approach number (3) as (in this case) the number of fields that are not in common are similar in nature and so won't be overly redundant, but is this the best approach? At what point (how many fields) does it become a bad approach, and which would be the upgrade choice?

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

In reply to Mapping objects to database tables by clinton

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.