Table Design: Many searchable attributes

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Other SQL Server Topics (2005)

Table Design: Many searchable attributes

Author

Topic

deadeye
Starting Member

1 Post

Posted - 2008-07-09 : 12:27:02

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

mfemenel
Professor Frink

1421 Posts

Posted - 2008-07-09 : 13:06:53

I have no issue with your table layout, I think that's a pretty good and flexible design. You might consider a query more like this one though. Many "IN" statements are usually better served as joins. Also make sure you index your tables. I would say campgroundid would be good candidates for clustered indexes. Also if this were the real world I'd probably consider profiling and figuring out the most frequently run query and optimizing my code further for those parameters.
Create procedure queryCampgrounds
@hasBathrooms char(1),
@freeParking char(1)
as
select campgroundid,NumberofSites
from Campgrounds C
inner join CampgroundFeatureDetails d on c.campgroundid=d.campgroundid
where (@hasBathrooms='Y' or @hasBathrooms is null)
and (@freeParking='Y' or @freeParking is null)

Mike
"oh, that monkey is going to pay"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources