我是否应该将所有数据放在一个表中以实现过滤或使用交界表

Question

我考虑使用接线表来避免重复数据库中的数据。

所以我有以下模式

公寓：

+----+-------+----------+------------------+        
| ID | Price |  CityID  |  ContractTypeID  |       
+----+-------+----------+------------------+        
|  1 |  200  |    1     |         1        |    
|  2 |  150  |    1     |         2        |
|  3 |  400  |    1     |         2        |  
+----+-------+----------+------------------+ 

AnenityTypes：

+----+-------------+        
| ID |     Name    |      
+----+-------------+        
|  1 |  Building   |   
|  2 |  Kitchen    |
|  3 |  Furniture  |  
+----+-------+-----+ 

设施：

+----+----------+--------------+    
| ID |  TypeID  |     Name     | 
+----+----------+--------------+
|  1 |     1    |   Security   |
|  2 |     1    |   Parking    |
|  3 |     2    |   Fridge     |
+----+----------+--------------+ 

公寓门：

+-------------+-----------+    
| ApartmentID | AmenityID |     
+-------------+-----------+
|      1      |     1     |
|      1      |     2     |
|      2      |     1     |
|      2      |     2     |
|      2      |     3     |
+-------------+-----------+ 

我现在的问题，例如，如何应用一些过滤器，例如我需要CityID 1，Price＆Lt＆Lt;400，有冰箱和安全性。

这也是这是达到我想做的最好的方法DB设计的方法。

预期结果：

AppartmentID：2，建筑物功能：安全性，厨房功能：冰箱

更新：我当前的工作方法

SELECT Price, ID, Amenities.Name as 'Amenity' FROM Apartments
INNER JOIN ApartmentAmenities ON ApartmentAmenities.ApartmentID = Apartments.ID
INNER JOIN Amenities ON ApartmentAmenities.AmenityID = Amenities.ID
WHERE PropertyAmenities.AmenityID IN (1,2) AND Apartments.Price < 300 AND CityID=1

注意：我在此处发布的数据库中使用了不同的数据，但我的重点是方法

Answer 1

您可以在便利设施上使用过滤器，然后每间公寓进行组，并检查是否满足所有过滤器。然后加入您的公寓以获取其他过滤器。这样的东西。SQL小提琴

SELECT AP.ID
FROM Apartments AP
INNER JOIN 
(
    SELECT AA.ApartmentID
    FROM ApartmentAmenities AA
    INNER JOIN Amenities A ON A.ID = AA.AmenityID
    INNER JOIN AmenityTypes AT ON AT.ID = A.TypeID
    WHERE (AT.Name = 'Kitchen' AND A.Name = 'Fridge') OR (AT.Name = 'Building' AND A.Name = 'Security')
    GROUP BY AA.ApartmentID
    HAVING COUNT(DISTINCT A.Name) = 2
)AA 
ON AP.ID = AA.ApartmentID
WHERE CityID = 1 AND  Price < 400

您可以参考这些链接以获取更多信息。

https://www.simple-talk.com/sql/learn-sql-server/high-performance-division-division-division-indivision-incl-sql-server/

https://www.simple-talk.com/sql/t-sql-programming/divided-we-stand-sql-sql-sql-sql-relational-division/