Am I correct in assuming that "Industries" can be seen as some sort of
"super contact-group" in your application?
If so, you could merge "Contact group" and "Industries" --> "Contact group"
Contact Groups
------------------
id
name
parent_id (FK)
type ("industry_level, "sub_level", "some_other_level")
this would enable a tree of nested contact-groups, where your top contact
group is the "Industry group" as you mentioned.
Then Contacts would become:
Contacts
------------------
id
name
email
contact_group_id(FK) (the lowest level group a contact belongs to)
this would enable you to fetch contacts:
1. for some low-level group, e.g.:
select * from contacts as c, group as g1 where c.contact_group_id=g1.id and
g1.name='some group'
2. for industry as well when industry is one level above contact group if
you're still following, e.g.:
select * from contacts as c, group as g1, group as g2 where
c.contact_group_id=g1.id and g1.parentid=g2.id and g2.name='some industry'
you can see it becomes rather unelegant for higher levels
(g1.parentid=g2.id and
g2.parentid=g3.id.... g(n-1).parentid=gn.id....
Alternatively, you could create a NxN lookup table between contacts and
contactgroups, like:
Contacts
------------------
id
name
email
Contact Groups
------------------
id
name
type ("industry_level, "sub_level", "some_other_level")
ContactToGroupTable
---------------------------
id (autoinc)
contactid (FK)
contactgroupid (FK)
and on creation of a contact insert all relations (lowest contact group and
up the tree until you reach Industry-level) as seperate rows in
ContactToGroupTable.
Now you have abstracted away from difference between Industry and Contact
Group (Again, I assume that's how you use Industry) , and you're able to
have an aggregated group-in-group structure as deep as you like on which ou
can do queries like:
select c.* from contacts as c, contactgroup as g, contactToGroupTable as x
where g.name='some contactgroup or some industry' and g
<http://g.id/>.id=x.contactgroupid
and x.contactid=c <http://c.id/>.id
HTH,
Geert-Jan
2010/7/26 Marc Guay <marc.g...@gmail.com>
> I keep running into problems like this and have another example of it
> that might be clearer.
>
> I have 4 tables, Newsletters, Contacts, Industries, and Contact Groups.
>
> We send Newsletters to Contacts, either grouped by Industry or Contact
> Group.
>
> Contact Groups must be associated with an Industry. Contacts must be
> associated with an Industry, but not necessarily a Contact Group.
>
> For example, sometimes we would like to send a Newsletter to all
> Contacts who are in the real estate Industry, and sometimes, we only
> want to send newsletters to Contacts who are members of the Planet
> Earth Real Estate Board. So far, I have the following:
>
> Newsletters
> ------------------
> id
> content
> contact_group_id (optional)
> industry_id (optional)
>
> Contacts
> ------------------
> id
> name
> email
> industry_id (FK)
> contact_group_id (FK) (optional)
>
> Industries
> ------------------
> id
> name
>
> Contact Groups
> ------------------
> id
> name
> industry_id (FK)
>
>
> Are suggested solutions for this problem any different from those I've
> received for the previous example? The real problem is when a table
> (Newsletters) can be associated with 2 or more other tables (Contact
> Group or Industry).
>
> Thanks for any guidance.
> Marc
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=gbr...@gmail.com
>
>
