SQL design approach for searching a table with an unlimited number of bit fields

Question

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)