Many to Many

As an undergrad, I was one of four computer science education majors on campus. The school of educaiton really didn’t have any idea what we should be learning, so they sent us over to the school of applied science to get minors in systems analysis. It was probably the closest thing the school had to a computer science program. Though I didn’t know it at the time, the most useful of these courses would turn out to be the last one I took: systems design.

It was actually the first part of a two-part series, but as a minor, I only had to take the first one. In it, we had to design an information system. That meant figuring out what information had to be stored, and how it should be organized in the database. We learned a lot about normalized databases, and created many entity-relationship diagrams.

photo credit: moacirpdsp on FlickrLet’s say you’re designing a database that stores demographic information. Maybe one of the things you want to store is “email address.” Each email address is tied to a specific person. But each person can have multiple email addresses. That’s called a one-to-many relationship. In database terms, this might be done with two tables. One table would have the person’s name, address, and other info in it. The second would have a list of email addresses, and the IDs of the people they belong to. That way, the database could handle an unlimited number of email addresses for each person. This is much better than having fields like “email address 1”, “email address 2”, etc. There will always be someone who has more addresses than you have space for.

But what about phone numbers? Each person can have multiple phone numbers, but each phone number can also belong to multiple people. This is called a “many-to-many” relationship, and it’s a big no-no in fully normalized databases. What you would need to do is have one table with people in it, and one table with phone numbers in it, and a third table that provides an index between the two. That way, you can easily find all of the phone numbers associated with a particular person, and all of the people associated with a particular phone number.

In practical terms, this may be going a bit overboard, but consider student scheduling in a high school. A classroom can have multiple teachers. Teachers can have multiple classrooms. Students are assigned to multiple classrooms (though not at the same time). Many students are assigned to the same classroom at the same time. Two students could be taking the same course at different times with different teachers in different rooms. This is the kind of application where these things become important.

But that wasn’t what I wanted to talk about or anything. I just thought I’d mention it.

Twitter is a great one-to-many application. I can type a short message and quickly send it out to the many — err, okay, the umm… several — people who follow me. It also works reasonably well for many-to-one connections. They can reply to my message, and the reply will go back to me. But it doesn’t work well for many-to-many. That is, someone who is following me on Twitter can’t send a message to everyone else who is following me on Twitter.

Consider this scenario, brought up by Chris Hamady:

I’ve been mulling over for some time how we can better utilize all of our eyes, ears and knowledge within the context of a social network. Looking forward to this year’s eTech Ohio Technology Conference, I… started to wonder how we could make searching for things at the conference easier for one another.

I contacted… Ryan Collins with the idea to use a Twitter account that we can all follow. I shared… my desire for this to be as simple and easy to use as possible. Ryan ultimately came up with a very easy way for all of us to “stay connected” and share or inform each other of any tech treasures, cool events, or good information that we might find at the conference.

Here’s the idea: set up Twitter in such a way that everybody following a particular Twitter user can send messages to everyone else following that user. It’s a many-to-many relationship.

Here’s how Ryan did it: he set up a Twitter account for EtechOhio09. If you follow that account, it will immediately follow you back. Then, if you send a direct message to that account, it will re-post it automatically, sending it out to everyone who’s following EtechOhio09. So let’s say I find out that Apple is giving away a free iPod Touch to the next 100 people who come up and ask for one. (They’re not, as far as I know. This is just an example). I could send a direct message to EtechOhio09 about it, and everyone who follows that account would get the message.

Even if you’re not going to this conference, you could set up this system to work for your own group or your own event. Ryan has generously shared his code, along with great instructions for setting it up.