SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Guru momos's Avatar
    Join Date
    Apr 2004
    Location
    Belgium
    Posts
    919
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    normalize address data? (was "Back to the drawingboard")

    I used to design my person tables like this:

    Person(id,name,surname,street,nbr,city,postalcode,state,country,tel,fax,email,...)

    but people are comming up with several email's, or 2 addresses, or several phone-numbers,

    So now I'm planning to rework my table, my question is: Should I normalise till infinity here eg.:

    Person(id,name,surname,...)
    PersonAddress(personId,addressId) /*many to many, for a family (->same address*/
    Adress(id,street,nbr,city,postalcode,state,country)
    PersonTelfax(personId,telfaxId)/*many to many, for a family (->same home number*/
    telandfax(id,type,telfax)
    email(id,type,email)
    ...

    Or is there a better way to go about this? Sometimes replication seems better, but my question in a way would be where to draw the line?

  2. #2
    SitePoint Guru momos's Avatar
    Join Date
    Apr 2004
    Location
    Belgium
    Posts
    919
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    an other way I just thought about this would be:
    Group(id,personId,...) /*which would define eg: a family*/
    Groupperson(groupId,personId)
    GroupAddress(groupId,adressId) /*an address for a family or maybe even a firm, which can have several locations*/
    Person(id,name,surname,...)
    PersonAddress(personId,addressId) /*many to many, for a family (->same address*/
    Adress(id,street,nbr,city,postalcode,state,country)
    PersonTelfax(personId,telfaxId)/*many to many, for a family (->same home number*/
    telandfax(id,type,telfax)
    email(id,type,email)

  3. #3
    SitePoint Guru momos's Avatar
    Join Date
    Apr 2004
    Location
    Belgium
    Posts
    919
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    noone?

  4. #4
    SitePoint Guru momos's Avatar
    Join Date
    Apr 2004
    Location
    Belgium
    Posts
    919
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Just realised an other thing with my second way of working;
    When I have companies in there, some companies may have sub-companies.

    I first thought I'd solve this by Creating a table Supergroup, with a one to many relation to Group. But I think it's a better way still to create an extra table to define a relation between groups, like this:
    GroupGroup(groupId1,groupId2, relation)

    I'm gonna start the actual programming tonight, but if anyone has any remarks or other ways...

    Thanks

  5. #5
    SitePoint Guru momos's Avatar
    Join Date
    Apr 2004
    Location
    Belgium
    Posts
    919
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Found yet an other way of looking at it, now I have no address in the for the person-table, but only at group-level, every person is a group at its own or part of a group.
    Person(id,name,...)
    Group(id,type,...)
    Address(id,street,..)
    GroupAddress(groupId,AddressId)
    GroupPerson(groupId,personId)


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •