Consider searching a table that contains Apartment Rental Information: A client using the interface selects a number of criteria that are represented as bit fields in the DB, for instance:
- AllowsPets
- HasParking
- HasDeck
- ModernKitchen
etc..
We are facing a situation where each new client of our software has additional fields they want to allow their end users to search on. The number of bit fields could reach into the hundreds.
I have three approaches that I'm considering and hoping for input and/or a different approach.
- Current approach: Add more bit fields, sql queries are built dynamically and executed using EXEC:
SET @SQL = @SQL + 'l.[NumUnits],' exec(@SQL))
Continue to add more bit fields. (table with 300 columns?)
Represent the data as a series of bits in one field. I'm unclear on if this approach will work, consider the 4 sample bit fields I offered above. The field could look like this: 1011 which would indicate false for 'hasparking' but true for all others. What I'm unclear on is how you would structure a query where you didn't care if it was false or true, for instance 1?11 where the person searching needs 1,3 and 4 to be true but doesn't care if 'HasParking' is true or false.
Move to an Attribute based approach where you have a table 'AttributeTypeID' and a table PropertyAttributes, which joins the PropertyID to the AttributeTypeId, new bit fields are simply a row in the AttributeTypeID table.
some other approach? Is this a well known SQL design pattern?
Thanks for any help
KM- EDIT PER COMMENTS
attribute table has a few other rows in it and is called listingattributes
CREATE TABLE [dbo].[ListingAttributes](
[ListingID] [bigint] NOT NULL,
[AttributeID] [int] IDENTITY(1,1) NOT NULL,
[AttributeType] [smallint] NOT NULL,
[BoardID] [int] NOT NULL,
[ListingMLS] [varchar](30) NOT NULL,
[PropertyTypeID] [char](3) NOT NULL,
[StatusID] [varchar](2) NOT NULL,
PRIMARY KEY CLUSTERED
(
[AttributeID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]
;WITH GetMatchingAttributes AS
(
SELECT
ListingID,COUNT(AttributeID) AS CountOfMatches
FROM ListingAttributes
WHERE
BoardID = 1
AND
StatusID IN ('A')
AND
--PropertyTypeID in (select * from @PropertyType)
--AND
AttributeType IN (2,3,6)
GROUP BY ListingID
HAVING COUNT(AttributeID)=(3)
)
SELECT
count(l.listingid)
FROM Listing l
INNER JOIN GetMatchingAttributes m ON l.ListingID=m.ListingID
-- where
-- StatusID IN (select * from @Status)
--AND
--PropertyTypeID in (select * from @PropertyType) 1 1 0 NULL NULL 1 NULL 1 NULL NULL NULL 0.1934759 NULL NULL SELECT 0 NULL
|--Compute Scalar(DEFINE:([Expr1006]=CONVERT_IMPLICIT(int,[Expr1012],0))) 1 2 1 Compute Scalar Compute Scalar DEFINE:([Expr1006]=CONVERT_IMPLICIT(int,[Expr1012],0)) [Expr1006]=CONVERT_IMPLICIT(int,[Expr1012],0) 1 0 0.001483165 11 0.1934759 [Expr1006] NULL PLAN_ROW 0 1
|--Stream Aggregate(DEFINE:([Expr1012]=Count(*))) 1 3 2 Stream Aggregate Aggregate NULL [Expr1012]=Count(*) 1 0 0.001483165 11 0.1934759 [Expr1012] NULL PLAN_ROW 0 1
|--Filter(WHERE:([Expr1005]=(3))) 1 4 3 Filter Filter WHERE:([Expr1005]=(3)) NULL 2471.109 0 0.00440886 9 0.1919928 NULL NULL PLAN_ROW 0 1
|--Compute Scalar(DEFINE:([Expr1005]=CONVERT_IMPLICIT(int,[Expr1011],0))) 1 5 4 Compute Scalar Compute Scalar DEFINE:([Expr1005]=CONVERT_IMPLICIT(int,[Expr1011],0)) [Expr1005]=CONVERT_IMPLICIT(int,[Expr1011],0) 9185.126 0 0.01422281 11 0.1875839 [Expr1005] NULL PLAN_ROW 0 1
|--Stream Aggregate(GROUP BY:(.[dbo].[ListingAttributes].[ListingID]) DEFINE:([Expr1011]=Count(*))) 1 6 5 Stream Aggregate Aggregate GROUP BY:(.[dbo].[ListingAttributes].[ListingID]) [Expr1011]=Count(*) 9185.126 0 0.01422281 11 0.1875839 [Expr1011] NULL PLAN_ROW 0 1
|--Index Seek(OBJECT:(.[dbo].[ListingAttributes].[_dta_index_ListingAttributes_BoardID_ListingID__AttributeType_PropertyTypeID_StatusID_6_7]), SEEK:(.[dbo].[ListingAttributes].[BoardID]=(1)), WHERE:(.[dbo].[ListingAttributes].[StatusID]='A' AND (.[dbo].[ListingAttributes].[AttributeType]=(2) OR .[dbo].[ListingAttributes].[AttributeType]=(3) OR .[dbo].[ListingAttributes].[AttributeType]=(6))) ORDERED FORWARD) 1 7 6 Index Seek Index Seek OBJECT:(.[dbo].[ListingAttributes].[_dta_index_ListingAttributes_BoardID_ListingID__AttributeType_PropertyTypeID_StatusID_6_7]), SEEK:(.[dbo].[ListingAttributes].[BoardID]=(1)), WHERE:(.[dbo].[ListingAttributes].[StatusID]='A' AND (.[dbo].[ListingAttributes].[AttributeType]=(2) OR .[dbo].[ListingAttributes].[AttributeType]=(3) OR .[dbo].[ListingAttributes].[AttributeType]=(6))) ORDERED FORWARD .[dbo].[ListingAttributes].[ListingID], .[dbo].[ListingAttributes].[AttributeID], .[dbo].[ListingAttributes].[AttributeType], .[dbo].[ListingAttributes].[StatusID] 16050.41 0.09677318 0.0315279 26 0.1283011 .[dbo].[ListingAttributes].[ListingID], .[dbo].[ListingAttributes].[AttributeID], .[dbo].[ListingAttributes].[AttributeType], .[dbo].[ListingAttributes].[StatusID] NULL PLAN_ROW 0 1
(7 row(s) affected)