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?
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?
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.
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.