SQL 设计、连接类型和子类型

Question

我正在制作一个交通记录前端，它允许显示有关财产上特定车辆的信息以及搜索特定车辆，但我不确定继续进行数据库设计的最佳方法。我想要做的是能够根据车辆类型和该车辆的功能/子类型快速调出所有车辆记录。我不知道如何最好地从我的单一车辆记录中参考多个子类型/功能。这是一个简化的示例：

我有一个车辆表：

VehicleID, LicensePlate, TypeID, SubTypeIDs
1        ,   111111    ,   2   ,   ;2;;3;

一个车辆类型表：

TypeID, Type
  1   ,  Car
  2   ,  Semi

和一个车辆子类型表： Vehicles.Subtype

SubTypeID,  TypeID,   SubType
1        ,   1    ,   Coupe
2        ,   2    ,   Flat Bed
2        ,   2    ,   Sleeper

字段是一个 varchar，其中我当前只是删除引用 VehicleSubTypes.SubTypeID 的子类型。 .. 这个想法是，从前端，在列出可用选项时挑选出每个 SubTypeID，并查找引用的 SubType 字符串（即“Coupe”）以显示给用户，或者更重要的是，当搜索带有卧铺和平床的半挂车时，要包含子类型“%;2;%”和“%;3;%”子句，以仅获取包含这两种功能的车辆。我现在只是在考虑这个解决方案，因为我已经休假一年了，我的大脑已经停止工作了:)我确信这是糟糕的数据库设计！但对于我的生活，我想不出更合适的方法，而且我所有的谷歌搜索努力不断出现根本不适用的子类型示例，或者我错过了与（即具有多组联系人的人）的相似之处信息..人员显然应该是一个表，联系信息显然应该是另一个表，他们通过 personID 链接，等等）

编辑/结论：

感谢 Bort 扰乱了我的记忆并指出我指向链接表。我现在添加了一个表 Link_VehicleToSubTypes：

linkID, VehicleID, SubTypeID
  1   ,   1      ,    2
  2   ,   2      ,    10     //10 = Cargo (Semi)
  3   ,   2      ,    15     //15 = No Sleeper

此外，我还创建了以下存储过程来返回与我传递给它的所有参数（最多 10 个）匹配的车辆的 VehicleID - 这样我以后就可以加入这个信息与搜索我的车辆表的结果相比较，其中包括特定于车辆的信息，例如 Vehicle.Color，因此我可以过滤最终结果集：

ALTER PROCEDURE dbo.ReturnVehicleIDsMatchingSubTypes

    (
    @SubType1 int = NULL,
    @SubType2 int = NULL,
    @SubType3 int = NULL,
    @SubType4 int = NULL,
    @SubType5 int = NULL,
    @SubType6 int = NULL,
    @SubType7 int = NULL,
    @SubType8 int = NULL,
    @SubType9 int = NULL,
    @SubType10 int = NULL
    )

AS
    DECLARE @intNumberSubTypesToMatch int SET @intNumberSubTypesToMatch = 
    (SELECT COUNT(@SubType1) 
            + COUNT(@SubType2) 
            + COUNT(@SubType3) 
            + COUNT(@SubType4)
            + COUNT(@SubType5)
            + COUNT(@SubType6)
            + COUNT(@SubType7)
            + COUNT(@SubType8)
            + COUNT(@SubType9)
            + COUNT(@SubType10))

    SELECT  VehicleID
    FROM    Link_VehicleToSubTypes
    WHERE
            SubTypeID IN (@SubType1, @SubType2, @SubType3, @SubType4, @SubType5, @SubType6, @SubType7, @SubType8, @SubType9, @SubType10)
    GROUP BY VehicleID
    HAVING        (COUNT(*) = @intNumberSubTypesToMatch)

    RETURN

我已经对此进行了测试，并且运行良好。我的存储过程的实现可能有点奇怪（我以前从未需要计算非空参数，这个方法就是我想到的），但它有效。 Bort - 当我有足够的积分时我会给你+1！非常感谢您的帮助！

Answer 1

由于像使用子类型一样将多个值存储在单个列中几乎总是一个坏主意（需要执行 LIKE '%;2;%' AND LIKE '%;3;%' 是一个巨大的任务红旗），如果我正确理解了您的要求，这看起来像是多对多关系，通常涉及一个交叉表来链接两个实体，在本例中为车辆和子类型。

如果您从车辆表中删除了 SubTypeID，并创建了一个链接表 Vehicle_Subtypes，

VehicleID   SubTypeID
1           2
1           3

那么您将能够编写查询来获取给定车辆的适当子类型，例如

SELECT S.SubType FROM SubTypes S
    INNER JOIN Vehicle_Subtypes X ON X.SubTypeID = S.SubTypeID
WHERE X.VehicleID = @VehicleId

可能需要更多逻辑来解释 TypeID，但这似乎是正确的设计。

编辑：我收回一切。误解了从子类型到车辆的部分，把它放在我的脑海里。反之则更困难，查找满足可变数量条件的记录可能会很棘手。给定相同的链接表（假设您对生成的 SQL 有很多控制权），您可以编写有点黑客的查询

SELECT VehicleId FROM Vehicle_Subtypes 
WHERE SubTypeId IN (1, 2)
GROUP BY VehicleId
HAVING COUNT(*) = 2

您必须确保 COUNT(*) = 选择的子类型数量，这样车辆就拥有了所有这些。不过，感觉应该还有更好的办法，我会继续思考。

Answer 2

我会做这样的事情：

• 一个包含车辆 ID、LicencePlate 和 TypeID (FK) 列的车辆表。车辆ID为PK。
• 包含 TypeID、SubTypeID 和 SubType 列的 VehicleSubTypes 表。 TypeID 和 SubTypeID 是 PK 的
• VehicleFeatures 表，其中包含车辆 ID (FK)、TypeID 和 SubTypeID 列。 TypeID 和 SubTypeID 是 VehicleSubType 的 FK。您的 PK 可以自动生成。

通过这个设计和一些SQL，你可以获得你想要的数据。