Cofa Posted March 8, 2014 Share Posted March 8, 2014 Hello all, I have been wondering if this is even possible: Say, a) I have a database containing real estate licensees ("members") and their real estate listings; members can form groups and each group will have a unique group name ("team1", "team2", etc); c) listings of a members that are linked to a particular group name of that member can be displayed in the website of other members of the group (e.g., listings that are linked to the group name "team1" of all members of the group can be retrieved by any member in the group using the "team1" group name); and d) I store the group names in the column "group_name" in table "members". Now my problem is, say if a member belongs to two groups ("team1", "team8"), 1) can I store both "team1" and "team8" in the "group_name" field under that member? 2) how could his listings be retrieved and displayed in different group members' sites by either of these group names? My goal is to allow members to have more than one group name, and his /her listings will be retrieved by different group members quoting the appropriate group names. How could I achieve this? Thanks, Cofa Link to comment Share on other sites More sharing options...
HartleySan Posted March 8, 2014 Share Posted March 8, 2014 You need to use an intermediary table for members and groups. For example: members id member_name 1 member 1 2 member 2 3 member 3 groups id group_name 1 group 1 2 group 2 members_groups member_id group_id 1 1 2 2 3 1 3 2 By the above data (and the proper SQL query), you can easily determine that member 1 is part of group 1 only, member 2 is part of group 2 only, and member 3 is part of groups 1 and 2. Make sense? Link to comment Share on other sites More sharing options...
Cofa Posted March 8, 2014 Author Share Posted March 8, 2014 Thanks HartleySan for the quick reply (and how are you? it's been a while ) ! I have not set up the database tables yet but figure that what I need to do next could be as follows... I'll set up a listings table and use the SQL query to retrieve the listings of a specified group, like this: listings id member_id 1 1 2 3 3 1 4 1 5 3 6 2 SELECT l.*, m.id, g.id, mg.group_id FROM listings l, members m, groups g, members_groups mg WHERE g.id = '2' ##We are to get all listings of group 2 AND mg.group_id = g.id AND l.member_id = mg.member_id GROUP BY l.id ORDER BY l.id DESC; Could you predict any wrong in the above code? Thanks! Link to comment Share on other sites More sharing options...
HartleySan Posted March 8, 2014 Share Posted March 8, 2014 Given your situation, I would have a table for groups, a table for members and a table for listings. I would then have two intermediary tables. One would be between groups and members, since that's a many-to-many relationship, and then another table between members and listings, since that's a one-to-many relationship. Make sense? Link to comment Share on other sites More sharing options...
Cofa Posted March 9, 2014 Author Share Posted March 9, 2014 Thanks again for the quick reply! I think I'll need some time to figure out how to do the query part.It's good to know the direction so far. Have a great day! Link to comment Share on other sites More sharing options...
Cofa Posted March 9, 2014 Author Share Posted March 9, 2014 After some thinking, I still don't understand why the need of "another table between members and listings," since I already have table listings consisting of this relationship. Any hint please? (Note: the table listings will also consist of other columns like property_type, address, age, etc.) I still haven't had a chance to experiment the query as I have not built the tables yet. But I have yet already had another query code (below) - wonder if there is any error. Thanks. SELECT m.id, g.id FROM members AS m, groups AS g WHERE g.id = 2 ##We are to get all listings of group 2 GROUP BY g.id LEFT JOIN members_groups AS mg ON ( g.id = mg.group_id ) LEFT JOIN listings AS l ON ( l.member_id = m.id ) ORDER BY l.id DESC Link to comment Share on other sites More sharing options...
HartleySan Posted March 9, 2014 Share Posted March 9, 2014 Yeah, that's a good point. You actually don't need another table between members and listings. My bad. I don't know why I thought that, but after thinking about it again, since it's a one-to-many relationship, you can simply add the member_id to each listing, and you'll be fine. Thanks for mentioning that. Link to comment Share on other sites More sharing options...
Cofa Posted March 10, 2014 Author Share Posted March 10, 2014 Thanks for following up on this. I'll check out the query code when have chance and update here again. Have a great day! Link to comment Share on other sites More sharing options...
Cofa Posted April 21, 2014 Author Share Posted April 21, 2014 Below is my final result of this topic - four tables and two query options... members m_id m_name 1 David 2 Jennifer 3 Mary groups g_id g_name 1 Group1 2 Group2 mem_group m_id g_id 1 1 2 2 3 1 3 2 listings l_id m_id type 1 1 House 2 3 Condo 3 1 Condo 4 1 Townhouse 5 3 Condo 6 2 Condo 7 3 Condo ----------------------- SUCCESSFUL QUERY CODE: ----------------------- SELECT l.* FROM listings AS l LEFT JOIN members AS m ON l.m_id = m.m_id LEFT JOIN mem_group AS mg ON m.m_id = mg.m_id WHERE mg.g_id = 2 ORDER BY l.l_id DESC ----------------------- SELECT * FROM mem_group AS mg LEFT JOIN listings AS l ON mg.m_id = l.m_id WHERE mg.g_id = 2 ORDER BY l.l_id DESC Link to comment Share on other sites More sharing options...
HartleySan Posted April 21, 2014 Share Posted April 21, 2014 That looks good, Cofa. My one comment is that for the listings table, if m_id combined with one or more other columns in the table is always unique, you can set that as your unique index, and probably get rid of the l_id column. With that said though, depending on how you're accessing the data, the l_id column could be beneficial. Well, if it works for ya, no complaints. Link to comment Share on other sites More sharing options...
Cofa Posted April 21, 2014 Author Share Posted April 21, 2014 Hi HartleySan, thanks for your comments. The l_id in the listings table is the primary key (auto assigned and auto incremented whenever a new listing is created). In the listings table each record will have many more other columns (property age, price, room measurements, etc, etc) that are not shown here. For my learning sake, I don't understand why you see m_id in the listings table could be unique, as each member ('m_id') can have many unique listings in the table (only the 'l_id' is unique). Thanks. Link to comment Share on other sites More sharing options...
HartleySan Posted April 21, 2014 Share Posted April 21, 2014 You're right that m_id by itself is not unique, but like I said, if m_id in combination with one or more other columns is unique, then you don't need the l_id. Link to comment Share on other sites More sharing options...
Cofa Posted April 21, 2014 Author Share Posted April 21, 2014 Mmmm. Each combination of m_id and other columns can be a unique record (listing). If we don't have l_id (listing id), how could we call (identify) a record from the table then? I must miss something here and am interested in knowing more. Thanks. Link to comment Share on other sites More sharing options...
HartleySan Posted April 22, 2014 Share Posted April 22, 2014 I know this isn't the case, but let's say that hypothetically the combination of m_id and type were always unique. That being the case, you can set up a unique index that spans those two columns (please see the documentation for how to create multicolumn indexes), and then you're SELECT queries would be like the following: SELECT * FROM listings WHERE m_id = ? AND type = ?; That make sense? With that said, if you know the l_id, which is the primary key, that will always be a quicker select because it's the primary key and essentially just a key-value lookup, but depending on how the table data is used in relation to other tables/data, the multicolumn index without the l_id column might make more sense. Link to comment Share on other sites More sharing options...
Cofa Posted April 23, 2014 Author Share Posted April 23, 2014 That's a new learn for me - thanks for that! And for your time! Link to comment Share on other sites More sharing options...
Recommended Posts