Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.

sanuglia's avatar

Join Tables or something else?

I'm hoping that someone could help me out with a strategy here.

I have an Account model, and a Client model, and there's a many-to-many relationship between those two. An account may have many clients, and a client may have many accounts. So far, so good.

But as I delve deeper into the details, I realize that an Account may have one or more email addresses. Likewise for a Client. Fine, so I abstract out an Email model. Then it dawns on me that I can abstract out a Phone Number, and maybe even an IM address. And now I have three more tables.

The emphasis here is that an Account can have zero or many emails, phone numbers, and/or IM addresses.

Well, I do see that there's a commonality between those three; namely that they're "Digital addresses" of sorts. So I could abstract out to a single table like this:

digital_contacts
=============
id
contact_type (maybe an enum of ["phone", "email", "fax", "im"] as an EXAMPLE?)
contact_value (varchar 100 or something)

Hmm, not bad. But now, how to relate the data. Well, a pretty good solution would be to have a "join table" of sorts. Something along the lines of:

account_emails
=============
id
account_id
digital_contact_id

And then another for phone numbers:

account_phones
=============
id
account_id
digital_contact_id

And another for fax, im, and anything else that might come up in the future. Now I have a whole bunch of tables and models. It works, but it's starting to get uglier.

An alternative might be to ALTER the digital_contacts table so it looks something like:

digital_contacts
=============
id
contact_value
model_name
field_name

Where the field_name is the property of the class that it's relating. Ie. Account model, and primary_phone property. Yeaaaah,,...now we're going to have a whole bunch of methods to get at the data. E.g.

pub function getPrimaryEmailAddresses($id){
}
pub function getSecondaryEmailAddresses($id){
}
pub function getPrimaryPhone($id){
}

Again, it could work, and it feels like we're now loosing cohesion.

Then I go looking at Eloquent to see what it can help with, but I'm not finding much inspiration there either. I do see polymorphic relations, and that seems kind of what I think I need, and it may work for some of these relationships. In fact, the section on Polymorphic Many To Many Relations looks close, .. but i'm not convinced that's the most efficient/proper/correct way to do this.

So I'd like to ask for anyone's thoughts on this, and maybe suggest a good enough strategy to tackle these many, many-to-many relationships (pun intended.)

0 likes
4 replies
jimmck's avatar

Sounds like you need to store these in a QBit architecture. Some bucks will be needed for that Quantum computer however...

sanuglia's avatar

@jimmck Heh, thanks. I've already ordered a few, but they're still on back-order.

In any event, I re-read the docs, and I think that perhaps I was getting ahead of myself. Setting up many to many relationships, one at a time would work.

1 like

Please or to participate in this conversation.