On 9/18/06, Andy Davies <[EMAIL PROTECTED]> wrote:
Hi, I wonder how people would handle this design: family >--- property/ address [1/many to 1]
It's a messy thing to model. Like many models, the difficult thing to ensure you capture in the model is _time_. There are two (practically) unchanging entities here: property and persons. The other relations and roles are transient. Not to make a sad statement on our times, but a review of a 400-year family history seems to indicate this is more constant (the change that is, not the relations) than we might suspect. A person may belong to one or more families with a transient start and end time and even overlap. A person may be the designated head of household of that family for a period of time, too. People ---- M:M --- Family --- M:1 --- Property People: birth, death, date, birthname, current surname, etc (with possibly a transaction log for dates of change of data) Family: "family name", date-effective-start, date-effective-end, HeadOfHouseholdFK, SecondContactFK (these are two additional relationships from People::Family, also 1:M). If this table is "write-once" it can also serve as the history/audit table. There's an alternative design where the HeadOfHousehold and SecondContact attributes are migrated to the M:M table between People and Family. There's a tricky bit of validation to work out there on how to enforce all of the rules: A family must have only one HoH. The HoH cannot also be the SecondContact. It's messy either way, which makes me suspect we're not modelling the right thing. I know for your business you may need there to be a "family" but does a family have any attributes that make it appropriate as an entity? Perhaps there is only a property and multiple occupants, two of which have a specific designation: People --- M:M occupies ---- Property People --- 1:M HoH for ---- Property People --- 1:M 2ndC for --- Property -- Ted Roche Ted Roche & Associates, LLC http://www.tedroche.com _______________________________________________ Post Messages to: ProFox@leafe.com Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
