Multiple attribute design that need to be searchable

[Kevin]

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 that this may make for a very slow search
if there are thousands of entries to search through because of the multiple
queries. I have a feeling that this may be a bad design. Does anyone know
of a better way of doing this?

Thanks,

Kevin

[Roy Harvey (SQL Server MVP)]

This has been answered in microsoft.public.sqlserver.programming.
Please don't cross post independent messages. If you must post to
more than one group put BOTH group names on a single message.

Roy Harvey
Beacon Falls, CT

On Wed, 9 Jul 2008 10:49:27 -0600, "Kevin" <de*********@hotmail.com>
wrote:

>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 that this may make for a very slow search
if there are thousands of entries to search through because of the multiple
queries. I have a feeling that this may be a bad design. Does anyone know
of a better way of doing this?

Thanks,

Kevin