我应该将所有数据放在一个表中以实现过滤还是使用联结表

I thought about using a junction table to avoid repeating the data in my database.

我想过使用联结表来避免重复数据库中的数据。

So I have the following schema

所以我有以下架构

Apartments:

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

AmenityTypes:

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

Amenities:

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

ApartmentAmenities:

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

My question now, how to apply some filters for example I need all the apartments in CityID 1, Price < 400, Has a Fridge and Security.

我现在的问题是,如何应用一些过滤器,例如我需要CityID 1中的所有公寓,价格<400,有冰箱和安全。

Also is this is the best approach db-design wise to achieve what I want to do.

这也是db-design明智的方法,可以实现我想做的事情。

Expected Result:

AppartmentID: 2, Building Features: Security, Kitchen Features: Fridge

公寓ID:2,建筑特色:安全,厨房特色:冰箱

Update: My current working approach

更新:我目前的工作方法

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

Note: I'm using a different data in my DB that posted here but my focus is about the approach

注意:我在我的数据库中使用了不同的数据,但我的重点是方法

1 个解决方案

#1

You can use do a filter on your amenities and then do a group per apartment and check if all the filters are satisfied. then join back to your apartment for additional filters. Something like this. SQL Fiddle

您可以在您的设施上使用过滤器,然后每间公寓进行一组,并检查是否所有过滤器都满意。然后加入公寓以获取更多过滤器。像这样的东西。 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

You can refer these links for more info.

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

https://www.simple-talk.com/sql/learn-sql-server/high-performance-relational-division-in-sql-server/

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

#1