Many-to-Many selecting rows based on what Table is linked

[Phillip Boushy]

I have two tables ("Schools" and "People") and I want to be able to have multiple email addresses associated with them. I would like to have one table "Email Addresses" and have a join table that can be used to find which email to pull based on the table and ID of the school/person. What is the best way to do this?

 

I believe it is possible to do this using global fields:

Schools

"Schools" (global field)

ID

Name

 

People

"People" (global field)

ID

First Name

Last Name

 

Email Addresses

ID

Type

Email

 

JOIN Object to Email

table ("People", "Schools")

Object ID

Email ID

 

But I'm curious if there is a better way to do this.

[comment]

Why won't you combine Schools and People into a single Contacts table?

[Phillip Boushy]

I only gave a small subset of each table. The full description of each table is listed below:

 

Schools

ID (serial)

School Name

Address 1

Address 2

City

State

Zipcode

Contract Signed On

Deployment Completed On

 

People

ID (serial)

First Name

Last Name

Job Title

Role

 

I'm hoping to come up with a solution that allows me to do this for additional tables in the future. (e.g. Districts)

 

I also believe that tables should be built after a single object, with relationships to other objects.

 

Each school will actually have multiple people. Each school can have at least one email address, but may have multiple. Each person can have multiple emails, etc.

[comment]

I also believe that tables should be built after a single object, with relationships to other objects.

 

That's true. So strictly speaking, you should have a super-type table of Contacts and two sub-type tables for Schools and for People, with a one-to-one relationship between them. However,it's often more convenient to have just one table and leave some of the fields empty, according to the type of contact.

 

There's also a question whether a school really has e-mail addresses as such - or are these addresses assigned to various contacts within the school, i.e. to people only.

 

 

In any case, going with your arrangement for a moment, I believe it could be simpler if your EmailAddresses table had these fields:

• SerialID

• SchoolID

• PersonID

• Type (i.e. work, home, etc.)

• EmailAddress

 

Don't quite see why you would need a join table here: are any of these tables in a many-to-many relationship with each other?

[Phillip Boushy]

Yes, many schools have an "Info@schoolname" account that is checked by multiple people, but it is fairly rare that we interact with that contact.

 

I guess you're right, a user would have multiple emails, but an email wouldn't have multiple users or schools...

 

So with that in mind, I should create a relationship with the following?

Schools->Email Address (SchoolID)

and 

People->Email Address (PeopleID)

[comment]

Yes, if you're going with that structure.