[eluser]North2Alaska[/eluser]
[quote author="North2Alaska" date="1332201162"]I'm working on the Contact information of our system. I have a contacts table and an attributes table. The idea is the attributes table can hold a phone number, email address, IM account, or dates of some kind. Rather than a single attributes model, I'm thinking I would like to have a phone model that selects attributes of type phone.
Is this a reasonable plan or is there a better/more preferred way of doing this?[/quote]
OK, digging deeper into the documentation. I'm reading the advanced relationships section, and I'm getting close, but not there yet. It's a little more complicated than I detailed above. There are three tables involved; contacts, resources, and attributes.
Contacts is more than just this but this is all we need for this exercise.
CREATE TABLE `ab_contacts` (
`id` bigint(20) NOT NULL AUTO_INCREMENT
) ;
The attributes table is a simple list of type and value. Type may be phone, email, etc... and value may be work, home, mobile, etc...
CREATE TABLE `ab_attributes` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`type` varchar(25) DEFAULT NULL,
`value` varchar(25) DEFAULT NULL
) ;
Now we have a table to join them up called resources. As I type this I see that I may need to rename the table, but let's continue for the moment. Here we have just a value field to store the entered data.
CREATE TABLE `ab_resources` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`contact_id` bigint(20) DEFAULT NULL,
`attribute_id` bigint(20) DEFAULT NULL,
`value` varchar(255) DEFAULT NULL
) ;
We join the table with the contact_id to the contacts table and attributes table. This is really a many to many table with an extra field for the value related to the relationship. I can use the set_join_field function to populate this value. I'm good so far.
So, here is where I think the complexity is. When I query for the resources, I don't want all the resources, I just want the phone numbers for example. And there may be specific things that need to happen to the phone number (formatting it). To me this sounds like a model. But I don't see how to create a Phone model where only the resources with a related type of Phone are the results of the model.
I'm still reading the docs, but in the absence of a lot of testing, I'm not seeing it. Any pointers?