StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POStoring many to many relationship objects in a column on database
text
Body
copied!<p>I have an object called listings, and a listing can have a number of amenities (compare this to post, tags model). It's a sort of has/doesn't have relationship. So I have an <code>amenities</code> table which stores what amenities there can be in the system, there is the <code>listings</code> table that stores listings, <code>listing_amenities</code> which stores amenities. </p> <p>The problem is searching listings on the basis of amenities</p> <p>I can search listing on the basis of any of the parameters of listing(rent, location, etc), but not amenities since that requires a 2 table join. I haven't really been able to come up with an efficient solution to do this. So, I'd like a little help on how to search on this basis. </p> <p>One solution I could think of was to inject an extra field with group concat of amenities</p> <pre><code> SELECT * FROM(SELECT listings.*, GROUP_CONCAT(CONCAT("(",Concat(amenity_id,")"))) as amenities FROM `listings`,`listing_amenities` WHERE listings.id=listing_amenities.listing_id GROUP BY listings.id) as rs WHERE amenities like "%(1)%" </code></pre> <p>i.e concatenate and add amenity ids wrapped in () and search on them </p> <p>which gets me to thinking: amenities are only used in context of listing and for searching so why not store them in a similar format (1), (2) to indicate available amenities in a seperate column in the listings table and save on a table and extra query cost? What are the disadvantages of this approach, if any?</p> <p>For an easier understanding of table </p> <pre><code> Listing Table -------- id title description rent Amenities ------------- id name description Listing_Amenities ------------------ id listing_id amenity_id </code></pre> <p>I am thinking I could delete the third table</p> <p>and modify listing</p> <pre><code> Listing Table -------- id title description rent amenties(the amenities available in the appartment wrapped in bracket) </code></pre> <p>like </p> <pre><code> 1 "House in Downtown Discworld" "Nice house, running water, teleporter" 2000 "(1)(5)(7)" </code></pre> <p>This kind of allows me to search by amenities without needing a join. I can run the same query where I select rent</p> <p>and search with the where clause: <code> where amenities like "%(1)%" AND amenities like "%(2)%"</code></p> <p>The only problem I see in this approach is a deletion anomaly i.e if I delete an amenity from system, it won't delete amenity from listings automatically</p> <p>but I see this as an unlikely occurence since amenities are decided by the admin and will be pretty much frozen before site launch and secondly, while we might add new amenties, its unlikely we'll delete any.</p> <p>Are there any other disadvantages. Can you please highlight specific scenarios. I see this as more efficient than a join. And frankly easier to wrap my head around. I could be wrong</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload