Multiple attribute design that need to be searchable

Last Post 09 Jul 2008 08:16 AM by deadeye0909. 0 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
deadeye0909
New Member
New Member

--
09 Jul 2008 08:16 AM
Hello,

I'm doing a school project and I'm supposed to be able to track campgrounds and their amenities. Here is some of the information I'm supposed to store:

CAMPGROUND
Number of Sites Number
Summer Season End Date
Summer Season Start Date
Reservations Accepted Y/N
Firewood Rate Number
Quiet Hours Text
15 Amp Service Y/N
30 Amp Service Y/N
50 Amp Service Y/N
Cable/Satellite Hookup Y/N
Fire Pits Y/N
Flush Toilets Y/N
Free Parking Y/N
Handicap Access Y/N
Hot Showers Y/N
and so on and so on (there are quite a few amenities that I'm supposed to track)

Now, the first thing I was thinking of was to store all of this in one table and for each amenity they have, the value would be true, while the ones they had would be false. Now, the problem with storing these attributes in the same table means that I cannot add a “description”, or a note with additional information from the owner about the amenities, or even an image if we’d like that displayed instead of the text. The way that I thought of having that was to add the amenities, facilities, and restrictions (anything that has a Y/N value above) into a “Features” table, then join a feature to a campground with a CampgroundFeatureDetail table (then we could also group the features by amenities, facilities, hookups, restrictions, etc. by adding the CampgroundFeatureType table). This way, if a campground has a certain amenity, then it will exist in the CampgroundFeatureDetail table with the CampgroundID. This would also mean that by not entering each feature manually into the campground table, it is much easier to add a new feature. The data access layer won’t need to be changed, as well as the HTML as the data displayed would be dynamic.

The problem that I found with this technique is that if someone is using the search form and tries to search for a site with multiple amenities (they want a campsite with a Free Parking, Handicap Access, Hot Showers, Internet Access, Paved Pads, Paved Roads, and that allows Pets, for example), then the only way to find a campground that meets those requirements is if an entry exists in the details table for that location. That produces a search like this:

SELECT CampgroundID, NumberOfSites, etc…..
FROM Campground
WHERE CampgroundID in (SELECT CampgroundFeatureDetailCampgroundID FROM CampgroundFeatureDetail Where CampgroundFeatureDetailCampgroundFeatureID = ‘F71881BF-E835-4117-AAB1-DE2619D493V’)
AND CampgroundID in (SELECT CampgroundFeatureDetailCampgroundID FROM CampgroundFeatureDetail Where CampgroundFeatureDetailCampgroundFeatureID = ‘08D00648-D842-43DF-A238-A591FF14B044’)
AND CampgroundID in (SELECT CampgroundFeatureDetailCampgroundID FROM CampgroundFeatureDetail Where CampgroundFeatureDetailCampgroundFeatureID = ‘9D9JF439-F2SD-F2V9-9N6G-G5F4F65L9KJ8’)
AND CampgroundID in (SELECT CampgroundFeatureDetailCampgroundID FROM CampgroundFeatureDetail Where CampgroundFeatureDetailCampgroundFeatureID = ‘9L0HK43D-FJ39-90FD-LD90-FKJ342JKL98UJ’)
AND CampgroundID in (SELECT CampgroundFeatureDetailCampgroundID FROM CampgroundFeatureDetail Where CampgroundFeatureDetailCampgroundFeatureID = ‘82LKJ342L-JK32-FD34-DF34-MJK9823JKN34’)
AND CampgroundID in (SELECT CampgroundFeatureDetailCampgroundID FROM CampgroundFeatureDetail Where CampgroundFeatureDetailCampgroundFeatureID = ‘NM23442-FHD4-FDJ3-AS21-SAER532SAD34’)
AND CampgroundID in (SELECT CampgroundFeatureDetailCampgroundID FROM CampgroundFeatureDetail Where CampgroundFeatureDetailCampgroundFeatureID = ‘JK324320-FDS3-FKL2-A348-DF23ZX43DF345’)
…..and so on for every feature they are searching for.

Now, what I'm concerned about is tha


Acceptable Use Policy
---