in reply to Mapping objects to database tables

Maybe it's just me but I would design the db first and the object layer second - that way external tools (sql, report builders, etc) do not have to filter through your mapping.

So for your example, I would modify example 1 ... but add a third table called Person and pull the person data out of Death and Wedding: 

    Table:Death     Table:Wedding          Table::Person
    -----------     -------------          -------------
    death_id        wedding_id             person_id
    person_id       groom_id               firstname
    short_obit      bride_id               surname
    obituary        message_from_couple
    flowers         details           
    charities       date_of_wedding
    date_of_death

[download]
Where groom_id and bride_id are foreign keys into Table:Person

-derby

Replies are listed 'Best First'.
Re^2: Mapping objects to database tables
by clinton (Priest) on Nov 07, 2006 at 13:35 UTC
    That makes a certain amount of sense, but doesn't give me the whole solution. I have left the firstname/surname as ordinary text fields because they have no more relevance than as text. There could be a thousand John Smiths who have nothing to join them, and there is no way of knowing whether John Smith 1 is the same as John Smith 2.

    Also, that change doesn't help me as far as specifying a list of properties particular to each announcement sub-class.

[reply]

      Okay ... for your problem domain, pull them back into the Death and Wedding table (but name them something better than xxx_1 and xxx_2 --- groom_first and bride_first may become un-pc but I'd worry about the downstream maintenance dev finding my home address than a PC fascist).

      As for the abstraction to announcements - I'd model the domain before I'd model a model of the domain. Once you have a handle on the domain, then I'd look for further abstractions based on biz requirements. I've been burned way too many times by meta systems that make adding a new type fairly easy but asking a simple question like "how many deaths notices did we have for November" caused a monster headache of building a slew of objects and then asking each object are you of type "death" and did you occur in "november." While a straight sql to answer that question would take milliseconds - going through the object hierachy takes hours.

      -derby
[reply]
        Okay ... for your problem domain, pull them back into the Death and Wedding table (but name them something better than xxx_1 and xxx_2 --- groom_first and bride_first may become un-pc but I'd worry about the downstream maintenance dev finding my home address than a PC fascist).

        Actually, it's a lot closer than you think :) With gay marriage being legalised and given the number of homosexuals who are internet oriented, I suspect that a lot of our wedding announcements are going to come from that quarter. My own, for instance...

        I've been burned way too many times by meta systems that make adding a new type fairly easy but asking a simple question like "how many deaths notices did we have for November" caused a monster headache of building a slew of objects and then asking each object are you of type "death" and did you occur in "november." While a straight sql to answer that question would take milliseconds - going through the object hierachy takes hours.

        I couldn't agree with you more. Which is why I'm leaning toward option number 3.

        The one difference of opinion I have is that I think I should be designing the interface first, then the implementation, rather than the other way around. Putting the implementation first, in my experience, tends to produce data driven interfaces that can map poorly to application.

        I know what I want it to do, but now I want to figure out the best way of implementing it

[reply]
      ad John Smith's uniqueness:
      I think that derby's data structure exactly reflects described situation. Once you have two or more J.S. _without_ some related rows in other tables, you really cannot distinguish between them. But it is not disadvantage of derby's normalization, it is lack of informations. You can, for instance, add some external (real) unique identifier into database table Person (insurance number? passport number? e.t.c.)

      ad other:
      I don't understand exactly what do you need. But I think that you can create some database views or some more complicated (perl) structures representing derby's data structure in proper format...

[reply]
        Re extracting the person's name into a separate table....

        I could do it, but all it buys me is a small reduction in table size, and it costs me an extra join (or two) for every announcement lookup.

        It would be a bit like extracting a date into its own table.
[reply]

In Section Seekers of Perl Wisdom