the best way to store several values

[mrMarcus]

hello there .. i have a site in the works where i will be storing apartment listings/rentals .. now, for each listing the user can select their amenities and/or utilities available with their listing (as well as many other things, but those aren't in question) .. i started out by storing the values in two fields, Amenities and Utilities, with the values in each looking like this, (for Amenities without the quotations) "Fireplace|Stove|Backyard, etc...", and similar for Utilities.

 

then, i came to a point where i wanted to develop a way for users to search the listings in the database using checkboxes (with the checkboxes carrying the value(s) of those in the Amenities/Utilities fields), but having the results returned specifically to what they were searching for (if they checked off Fireplace, Stove, Backyard, i only wanted listings returned containing AT LEAST those values).

 

it ended up being quite a feat (for me at least), since i had to extract whatever was in those two fields and run an array_intersect against them using whatever values they had checked off, ultimately getting it to work .. the only problem i then ran in to was getting the results to paginate, since the actual results being queried were a different number than those actually being returned after the array_intersect.

 

- and breathe -

 

so, since both the Amenities and Utilities fields are going to contain upwards of 25 values each, here is my question...

 

what is the best way to setup my database to hold these values .. keeping in mind that there will be upwards of 25 or so values (Fireplace, Backyard, Stove, etc...) .. should i just setup two separate tables and join them afterwards to the listing upon the query?  or will the way i have been doing it work with pagination ultimately .. i have code if anybody would like to see.

 

thanks so much in advance.

[Mchl]

Have a schema like this:

 

table apartaments  //here you store information about apartaments
apartamentID, address, owner...

table amenities //here you store list of all available amenities
amenityID, amenityName

table apartaments_amenities //here you store information on which amenities given apartament has
apartamentID, amenityID

 

similar for utilities

[mrMarcus]

thanks for the response

 

i figured that's what'd i'd end up having to do, i had just gone so far already and was really hoping to not have to backtrack .. it's better that i just get it done in a fashion where the values are easily accessible, and with what you said, that'd definitely be the case.

 

thanks again.

 

any other suggestions are welcome, otherwise it's all good.

[Mchl]

That's classical example of m-to-n relation. It lets you easily add new amenities and fairly easy remove those no longer unneeded.

When I was starting my work with databases I was wondering 'do I realy need three tables for this? there must be better way'. Seems there isn't