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'…

Accepted Answer

something like this may work for you:

define tables:

```
CREATE TABLE #Apartments
(
     ApartmentID    int          not null primary key identity(1,1)
    ,ApartmentName  varchar(500) not null
    ,Status         char(1)      not null default ('A') 
    --....
)

CREATE TABLE #AttributeTypes
(
    AttributeType         smallint     not null primary key
    ,AttributeDescription varchar(500) not null
)

CREATE TABLE #Attributes  --boolean attributes, if row exists apartment has this attribute 
(
     ApartmentID     int not null --FK to Apartments.ApartmentID    
    ,AttributeID     int not null primary key identity(1,1)
    ,AttributeType   smallint  not null --fk to AttributeTypes
)
```

insert sample data:

```
SET NO COUNT ON
INSERT INTO #Apartments VALUES ('one','A')
INSERT INTO #Apartments VALUES ('two','A')
INSERT INTO #Apartments VALUES ('three','I')
INSERT INTO #Apartments VALUES ('four','I')

INSERT INTO #AttributeTypes VALUES (1,'dishwasher')
INSERT INTO #AttributeTypes VALUES (2,'deck')
INSERT INTO #AttributeTypes VALUES (3,'pool')
INSERT INTO #AttributeTypes VALUES (4,'pets allowed')
INSERT INTO #AttributeTypes VALUES (5,'washer/dryer')
INSERT INTO #AttributeTypes VALUES (6,'Pets Alowed')
INSERT INTO #AttributeTypes VALUES (7,'No Pets')

INSERT INTO #Attributes (ApartmentID, AttributeType) VALUES (1,1)
INSERT INTO #Attributes (ApartmentID, AttributeType) VALUES (1,2)
INSERT INTO #Attributes (ApartmentID, AttributeType) VALUES (1,3)
INSERT INTO #Attributes (ApartmentID, AttributeType) VALUES (1,4)
INSERT INTO #Attributes (ApartmentID, AttributeType) VALUES (1,5)
INSERT INTO #Attributes (ApartmentID, AttributeType) VALUES (1,6)

INSERT INTO #Attributes (ApartmentID, AttributeType) VALUES (2,1)
INSERT INTO #Attributes (ApartmentID, AttributeType) VALUES (2,2)
INSERT INTO #Attributes (ApartmentID, AttributeType) VALUES (2,3)
INSERT INTO #Attributes (ApartmentID, AttributeType) VALUES (2,4)
INSERT INTO #Attributes (ApartmentID, AttributeType) VALUES (2,7)

INSERT INTO #Attributes (ApartmentID, AttributeType) VALUES (3,1)
INSERT INTO #Attributes (ApartmentID, AttributeType) VALUES (3,2)
INSERT INTO #Attributes (ApartmentID, AttributeType) VALUES (3,3)
INSERT INTO #Attributes (ApartmentID, AttributeType) VALUES (3,4)

INSERT INTO #Attributes (ApartmentID, AttributeType) VALUES (4,1)
INSERT INTO #Attributes (ApartmentID, AttributeType) VALUES (4,2)
SET NOCOUNT OFF
```

sample search query:

```
;WITH GetMatchingAttributes AS
(
SELECT
    ApartmentID,COUNT(AttributeID) AS CountOfMatches
    FROM #Attributes
    WHERE AttributeType IN (1,2,3)  --<<change dynamically or split a CSV string and join in
    GROUP BY ApartmentID
    HAVING COUNT(AttributeID)=3--<<change dynamically or split a CSV string and use COUNT(*) from resulting table
)
SELECT
    a.*
    FROM #Apartments                      a
        INNER JOIN GetMatchingAttributes m ON a.ApartmentID=m.ApartmentID
    WHERE a.Status='A'
    ORDER BY m.CountOfMatches DESC
```

OUTPUT:

```
ApartmentID ApartmentName 
----------- --------------
1           one           
2           two

(2 row(s) affected)
```

In the search query above, I just included a CSV string of atribute IDs to search for. In reality, you could create a Search stored procedure where you pass in a CSV parameter containing the IDs to search on. You can look at this answer to learn about loop free splitting of that CSV strings into table which you can join to. This would result in not needing to use any dynamic SQL.

EDIT based on the many comments:

if you add a few columns to the #AttributeTypes table you could dynamically build the search page. Here are a few suggestions:

- Status: "A"ctive "I"nactive

- ListOrder: can use this to sort by to build the screen

- ColumnNumber: can help organize fields on the same screen row

- AttributeGroupID: to group fields, see below

- etc.

You could make all the fields checkboxes, or add another table called #AttributesGroups, and group some together and use radio buttons. For example, since "Pets Allowed" and "No Pets" are exclusive, add a row in the #AttributesGroups table "Pets". The application would group the attributes in the interface. Attributes in Groups would work the same as regular ungrouped attributes, just collect the selected IDs and pass it in to the search procedure. However, for each group you'll need to have the application include a "no preference" radio button and default it on. This option will not have an attribute ID and is not passed in, since you don't want to consider the attribute.

In my example, I do show an example of a "super attribute" that is in the #Apartments table, "Status". You should only consider major attributes for this table. If you start using these, you may want to alter the CTE to be FROM #Apartments with filtering on these fields and then join to #Attributes. However you will run into issues of Dynamic Search Conditions, so read this article by Erland Sommarskog.

EDIT on latest…

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

Solution

Problem

Related problems