Need help with relationships (don’t we all).

[TheWiseGuy]

I am building a database to manage contacts, contracts and students information for a membership-based marital art’s business. I initially thought this would a simple task but, like most things, “the easy part is talking about it, the hard part is doing it”. Anyway, I am getting major brain cramps trying to determine the best way to separate contact names and addresses into individual tables and setup a many to many relationship while also avoiding “orphaned records”.

Here’s what I am trying to do. I need to setup a contact-names table and contact-address tablewith a "many to many" relationship. For example, a particular Contact-Name must have at least one related Contact-address record but may have several related Contact-Address records (i.e.: work address and/or home address). Likewise, a particular Contact-Address must have at least one related Contact-Name record but may have several related Contact-Names records (i.e.: Two students with the same home address).

This gets more complicated when I try to "tie-in" the contact information tables into a MEMBERSHIP-AGREEMENT table. Each MEMBERSHIP-AGREEMENT record must have one “purchaser”; at least one “student” and usually has several “emergency” contacts. In many cases, the purchaser and the student addresses are different. Lets say your entering a new contract, you would need to be able to enter the purchaser’s name and address, the student names and addresses and several emergency-contact names and addresses.

Here are my questions:
1) How would you setup a many to many relationship between the Contact-Name table and Contact-Address table
2) How would you avoid duplicate addresses?
3) How would you avoid orphaned names and/or addresses?

I know this is probably “simple-Simon stuff to most but any suggestions will be appreciated.

 

[JerryDennison]

The only time you can have a Many-to-Many relationship is when you actually have many records on BOTH sides of the relationship. In this case you would setup addresses(irrespective of who might live there) then setup people and then link the people to one or more addresses. I believe you're intellectualizing your scenario a little too much. Making it more complex than what it actually is.

Since a contact can be a purchaser, a student, and a contact you only need to have one table for the individual's information. You could then approach the multiple addresses in one of two ways, create a table with a one-to-many relationship between individuals and addresses(would you also want to setup a table to give them unlimited number of phone numbers?). Or, simply have a primary and secondary address and up to 4 phone numbers per individual.

The contract is where you would have defined a single individual of responsibility(finances wise) and a link table (one to many which gives you many to many) where you would add the students and emergency contacts(both of which are pulled from the individuals table too).

The alternative is to define your addresses separately and then link them to the individuals. What you would gain in data entry and db real estate would not be offset by an increase in db complexity and awkwardness.

 

[MAPetry]

Another way to define Many to Many relationships is to set up a recursive relationship table. Assign codes to home, work, mailing addresses, etc then set up an address table that refrences the (recursive) "address type code" and the Contact. This way you can have as many addresses or any other information you want for the contact. Just make sure that you set up seperate tables (ie Contact_Addresses) for each type of information.