Advice on matching values - SQL Server Forums

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 2000 Forums

SQL Server Development (2000)

Advice on matching values

Author

Topic

ajthepoolman
Constraint Violating Yak Guru

384 Posts

Posted - 2004-02-06 : 15:37:25

Hello all. I have a client that wants a matching feature in their software.

Here is how it would work:
This agency will enter information about daycare providers and families in need of daycare.
Providers have "features". These features are stored in separate tables since each Provider can have many features.
Families can specify which features are important to them.

I need to be able to say that for Family #1, they want a daycare that has an Air Purifier, No Pets, Fenced yard, offers 2nd shift care, and so on and so forth. My client then wants to hit a Search button and get a list of matching providers.

My thinking is this. I will load a table with all my providers listed in it. Then I will delete records from that table by eliminating anyone who does not have the same features that my family is looking for.

I am having a problem with that logic though. I am afraid that a provider will be eliminated completely from the search even though they have an Air Purifier, No Pets, Fenced yard, but don't offer 2nd shift care. So, even though they matched on three items, that provider won't come back because they dropped from my result set because they don't offer 2nd shift care.

I am not asking anyone to write anything for me. I am just trying to figure out the logic you would use on something like this. Those of you who helped me with my cursor migration know that my database is highly normalized. Foreign key constriaints all over the place.

It sort of complicates things since Air Purifier and Fenced yard are in an Environment table. While No Pets is in a Pets table. And 2nd shift care is in a Services table. So it is not as simple as checkboxes in the Provider table.

Any help you can offer would be appreciated!

Thanks!

Aj

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-02-06 : 16:46:29

Here is a technique which allows you to determine the match percentage of each provider. It uses a tally table technique ( http://www.sqlteam.com/item.asp?ItemID=2652 ) and an input parameter to match providers which contain any of the desired features and assigns a match percentage to that provider. This might be useful as you can return only those records which meet a certain percentage of feature matches. (ie give me all providers which match >=80% of the desired features.)

create table Numbers(n int)

SET NOCOUNT ON
Declare @i int
Set @i = 0
WHILE @i <= 8000
     BEGIN
         Insert Into Numbers Values (@i)
         Set @i = @i + 1
     END
SET NOCOUNT OFF   

create table features ( featureid int identity(0,1), featuredesc varchar(30))

insert into features
select 'Air Purifier' union all
select 'No Pets' union all
select 'Fenced yard' union all
select '2nd shift care'  union all
select 'Air Purifier' union all
select 'Fenced yard'

create table provider ( providerid int, featureid varchar(30) )

insert into provider
select 0,0 union all
select 0,1 union all
select 0,2 union all
select 0,3  union all
select 0,4 union all
select 1,5 union all
select 1,2 union all
select 1,3 


declare @features varchar(20)
set @features = '0,1,2,3,4'

SELECT NullIf(SubString(',' + @features + ',' , n , CharIndex(',' , ',' + @features + ',' , n) - n) , '') as desired_featureid
INTO #desired_features
FROM Numbers
WHERE n <= Len(',' + @features + ',') AND SubString(',' + @features + ',' , n - 1, 1) = ',' 
AND CharIndex(',' , ',' + @features + ',' , n) - n > 0
GROUP BY n

SELECT providerid, CONVERT(numeric(4,2),CONVERT(float,provider_features)/CONVERT(float,ttl_features))*100  percent_features
FROM
(
	SELECT providerid,count(providerid) provider_features, (select count(*) from #desired_features) ttl_features
	FROM provider p
	JOIN #desired_features d ON d.desired_featureid = p.featureid
	GROUP BY providerid
) d

drop table provider, features, #desired_features

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2004-02-06 : 17:27:44

select TOP 3 prid, count(*)
(
select prid from Airs where air IN (@air_req1, @air_req2, @air_req3)
union all
select prid from Pets where pet IN (@pet_req1, @pet_req2, @pet_req3)
union all
... ... ...
) z
group by prid
order by 2 desc

Also, it can be added some kind of numeric priority for each requirement
and then included into calculations along with "simple" aggregate counts.

ajthepoolman
Constraint Violating Yak Guru

384 Posts

Posted - 2004-02-06 : 17:30:52

ehorn, I put your code in Query Analyzer and can see how this would be of tremendous help.

My challenge now is to get around the id's. It would be great if all the features were in one table with their own unique key. But as it is, Air Purifier is in the Environment table with a PK of 1, and No Pets is in the Pets table with a PK of 1. So I need to come up with the feature "type". So it knows the difference bewteen 1 in Pets and 1 in Environment.

Perhaps instead of a single colum to compare features against, I have a column called PetFeatures that stores the IDs of the Pets features that the provider has. I would then add a column EnvironmentFeatures that holds the IDs of the Environment features that the provider has.

I will have to play around with some of this stuff!

Thanks!

Aj

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-02-06 : 17:39:26

Aj, There are many ways to accomplish this task as Stoad demonstrated another method. I like the CSV ids because you can have an unlimited number of features passed into the procedure without modifying the input params when features are added/deleted. Also another option to the featuretypeid might be to create an indexed view of all the features with a unique id, a featuretypeid, and the table featureid. Though not knowing the data schema makes it difficult to recommend the best strategy here.

ajthepoolman
Constraint Violating Yak Guru

384 Posts

Posted - 2004-02-06 : 17:59:28

Here is the jist of our system.

tblProviderFacility has intFacilityID along with name, code and other stuff.

xtblEnvironment has a PK and the names of the Environment features. So ID #1 is 'Air Purifier' and ID #2 is 'Fenced Yard' and ID #3 is 'Swimming Pool'.

xtblPets has a PK and the names of the Pets features. So ID #1 is 'No Pets' and ID #2 is 'Indoor Pets' and ID #3 is 'Outdoor Pets Only', so on and so forth.

Finally, I have a table called tblProviderFacility_Pets that contains it's own PK, and Foreign Keys pointing back to tblProviderFacility and xtblPets. So it has columns intProvFacPetID PK, intProviderID (FK to tblProvider) and intPetID (FK to xtblPets).

That is an overview of our schema. Too normalized? Probably, but little I can do about that!

Armed with that knowledge, you can probably see where I was going with the idea of adding extra columns to table features.

I was thinking along this line:
create table features ( featureid int identity(0,1), featuredesc varchar(30), PetFeatureID int, EnvironmentFeatureID int, MealsFeatureID int, ServicesFeatureID int)

I would then put the appropriate ID in it's appropriate column. The other columns would just get NULL values.

Aj

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-02-07 : 09:23:53

Here is an alternative to the feature table idea:


create table featuretype
(
	featuretypeid int identity(0,1) NOT NULL Primary key,
	description varchar(30) NOT NULL,
)

create table feature
(
	featureid int identity(0,1) NOT NULL Primary Key,
	featuretypeid int NOT NULL references featuretype(featuretypeid),
	description varchar(30) NOT NULL
)

insert into featuretype
select 'Pets' union all
select 'Environment' union all
select 'Meals' union all
select 'Services' 
--etc...

insert into feature
select 0,'No pets' union all
select 0,'Indoor pets only' union all
select 0,'Outdoor pets only' union all
select 1,'Air purifier' union all
select 1,'Fenced yard' union all
select 1,'Swimming pool'
--etc...

select ft.*,f.featureid,f.description 
from featuretype ft
join feature f on f.featuretypeid = ft.featuretypeid

drop table feature, featuretype

ajthepoolman
Constraint Violating Yak Guru

384 Posts

Posted - 2004-02-09 : 09:21:46

Thanks ehorn, I have meetings until this afternoon, but I will plug that code in and see how it works for me!

Thanks again everyone!

Aj

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources