Jump to content
Larry Ullman's Book Forums

Recommended Posts

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;

 

B) 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 post
Share on other sites

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 post
Share on other sites

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 post
Share on other sites

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 post
Share on other sites

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 post
Share on other sites

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 post
Share on other sites
  • 1 month later...

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 post
Share on other sites

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 post
Share on other sites

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 post
Share on other sites

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 post
Share on other sites

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 post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
×
×
  • Create New...