Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2003
    Posts
    2

    Question Unanswered: database design question

    hi, I'm stuck with database design, and come to know of this forum, hope the experts here can provide me an advice.

    I'm trying to design a database, to store the following type of information.

    - Person (name, identity no...)
    - Phone (phone type (mobile, residential, fax), number
    - Address (street, country...)
    - Person and phone relationship. A person can have 0 or more Phone, and the relationship can be user or subscriber.
    - Person and address relationship. A person can live in 0 or more address, and the relationship can be occupants, owner...
    - Phone and address relationship. A phone can be have 0 or more address, and the relationshp is installation address, billing address

    My design forms a circle, person - person_phone- phone - phoneAddress - Address - personAddress (-back to person). Is this a bad design, if it is, any solution to overcome?
    Attached Thumbnails Attached Thumbnails relationship.jpg  

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I see nothing wrong with the circular design, since the intermediary table in your many-to-many relationships will prevent relational integrity issues cascading around your design.

    A few questions, though...

    Can a phone have more than one User?
    Can a phone have more than one Subscriber?
    Can a phone have more than one Installation Address?
    Can a phone have more than one Billing Address?
    Are these the only two types of Phone addresses, or are there potentially an unlimited number of address types for phones?

    blindman

  3. #3
    Join Date
    Oct 2003
    Posts
    2
    thanks blindman for the assurance... coz I'm rather confuse, as I'm not sure will there be any hidden problems with this type of design.

    1. Can a phone have more than one User? yes
    2. Can a phone have more than one Subscriber? at any one time, no... but the number can be re-use (eg, the first subscriber unsubscribe, and another one subscribe to it)
    3. Can a phone have more than one Installation Address? similar to 2
    4. Can a phone have more than one Billing Address? similar to 2
    5. Are these the only two types of Phone addresses, or are there potentially an unlimited number of address types for phones? might have more, but not identified as of now.

    will this question affect my design?

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    There are some alternative designs for this type of modeling, but it looks to me like you are going to require all the flexibility in the circular schema you have. That just means you will need to store more data-integrity logic in the procedures you write for inserting, updating, and deleting records.

    See if anybody else has comments on this over the weekend. Otherwise, go with it.

    blindman

Posting Permissions

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