Loading ...
Sorry, an error occurred while loading the content.

Database Refactoring: Combining one-to-one

Expand Messages
  • Scott W. Ambler
    For the folks on the list, this is an interesting in-progress conversation I thought I d share. ... Doh! Missed that in your original posting. ... This makes
    Message 1 of 1 , Apr 6, 2004
      For the folks on the list, this is an interesting in-progress conversation
      I thought I'd share.

      At 09:37 AM 4/6/2004, you wrote:
      >Now I see that I had used a bad representation of my model.
      >Start using your notation.
      >Slightly changed model:
      >PersonTable(personKey, personData)
      >EmployeeTable(personKey, employeeData)
      >AddressTable(idMulti, forTable, addressData)
      >Example records in AddressTable:
      >{7, 'P', personAddress}
      >{7, 'E', employeeAddress}
      >EmployeeTable has personKey because it's 1-to-1 to PersonTable.
      >I argued against combining Person and Employee.

      Doh! Missed that in your original posting.

      >The schema would become:
      >PersonTable(personKey, personData, employeeData)
      >AddressTable(personKey, addressCount, addressData)

      This makes sense.

      >Records of Address:
      >{7, 1, personAddress}
      >{7, 2, employeeAddress}

      Seems that you have some data cleansing to do then. If the two addresses
      are the same then combine the rows.

      >Of course this is a mix of physical and semantic Information.

      That happens.

      >If I would keep my initial kind of arranging things
      >that 'E' from an address record would point now to a "virtual" table
      >whereas before every value in that column forTable
      >had pointed to a physical table.
      >In my initial data model there were associations of Person
      >and Employee to Address.
      >After combining Person and Employee you have a 1-to-2 relation
      >to Address which I find not so expressive.

      Perhaps you don't though. If the records are the same then combine, if
      they're different then that's the way it is. Probably an indication that
      you need a TypeTable because you have different types of address -- mailing
      address, vacation address, ...

      >Of course you can post our mails.
      >I'm just starting to look around the agile things
      >so I'm not finished yet to check for useful mailing lists.
      >Then I will post my stuff "official".
      >Thanks again.
      >Michael Rachow
      >Tel.: +49 40 665341
      >Mob.: +49 172 9017073
      >Von: Scott W. Ambler [mailto:scott.ambler@...]
      >Gesendet: Di 06.04.2004 14:41
      >An: Michael Rachow
      >Betreff: Re: A thought on combining one-to-one
      >I'm not sure that I see what your issue is.
      >If your original schema look something like:
      >PersonTable(personKey, personData)
      >EmployeeTable(employeeKey, employeeData)
      >AddressTable(idMulti, forTable, addressData)
      >Then your new schema becomes:
      >PersonTable(personKey, personData, addressData)
      >EmployeeTable(employeeKey, employeeData, addressData)
      >You'd effectively apply the refactoring twice.
      >Did I understand you correctly? Also, would you mind if I repost this to
      >the Agile Databases mailing list? It's a good issue.
      >- Scott
      >At 07:02 AM 4/6/2004, you wrote:
      > >Hi Scott,
      > >
      > >I just read in
      > >Agile Database Techniques
      > >the appendix: Database Refactoring Catalog
      > >the note on combining one-to-one tables.
      > >
      > >Obviously it doesn't matter if combining or not.
      > >But the data model looks different.
      > >
      > >Like you with Employee and EmployeeDetails
      > >I have person and employee.
      > >Both have an address (private, company).
      > >I'm dealing with that situation giving address a Pk as
      > ><IdMulti, ForTable>.
      > >For both tables I have an one-to-one association.
      > >IdMulti takes the key from an other table whereas
      > >ForTable names the table the records belongs to.
      > >In address I have 2 records: <7, 'P'> and <7, 'E'>
      > >
      > >For me this is a common manner of acting
      > >throughout the data model.
      > >Breaking it for example would yield to introducing "virtual" tables
      > >(in that case E(employee)) or similar stuff.
      > >Because until now every table named in ForTable was a physical one.
      > >
      > >As I said above,
      > >a thought only ...
      > >
      > >
      > >Kindly regards
      > >Michael Rachow
      > >_____________________
      > ><<mailto:michael@...>mailto:michael@...>michael@famra
      > chow.com
      > >Tel.: +49 40 665341
      > >Mob.: +49 172 9017073

      Scott W. Ambler
      Senior Consultant, Ronin International, Inc.

    Your message has been successfully submitted and would be delivered to recipients shortly.