I’m trying to think about the best way to handle group memberships on a website. People sign up and select checkboxes in a list of interests. Every week we send out interest-themed emails to those members that indicated that interest.
however i store the information in the database, while i am working with the lists and generating lists of email addresses or manipulating group memberships, the composite design pattern looked interesting.
it would be easy to populate the group, then do some aggregating functions that say… generate the list of email addresses based on the interests.
but i’m not sure i’m seeing any other advantages. i do need something scalable, and flexible.
thoughts?
1
Design patterns apply more to code, than to databases. When you have entered the database world, you simply want a normalised database. The biggest issue you have with the composite pattern is scale: in order to manipulate those OO objects, you’d need to pull all the data from the database regardless if you need it – which is a waste and impractical if you get large lists.
In your case:
users: id, name, email, etcinterests: id, descriptionusers_interests: user_id, interest_id
with the appropriate primary/unique keys. After that, you can get all of the information you need in a single database query.
For example:
-
All emails who are interested in
cycling(id=2):<code>SELECT email FROM usersJOIN user_interests ON user_interests.id = users.idWHERE user_interests.interest_id = 2</code><code>SELECT email FROM users JOIN user_interests ON user_interests.id = users.id WHERE user_interests.interest_id = 2 </code>SELECT email FROM users JOIN user_interests ON user_interests.id = users.id WHERE user_interests.interest_id = 2 -
All users who are interested in IDs
2, 3, 4and5:<code>SELECT email FROM usersJOIN user_interests ON user_interests.id = users.idWHERE user_interests.interest_id IN (2, 3, 4, 5)GROUP BY users.idHAVING COUNT(users.id) = 4</code><code>SELECT email FROM users JOIN user_interests ON user_interests.id = users.id WHERE user_interests.interest_id IN (2, 3, 4, 5) GROUP BY users.id HAVING COUNT(users.id) = 4 </code>SELECT email FROM users JOIN user_interests ON user_interests.id = users.id WHERE user_interests.interest_id IN (2, 3, 4, 5) GROUP BY users.id HAVING COUNT(users.id) = 4
However, many frameworks (e.g. CakePHP for PHP) will do this for you.
3