php - 验证 2 个值以确定 "Yes"或 "No"

Question

确定车辆是否可以驾驶的最佳方法是什么。

假设我们有不同的驾驶执照类型:A、B、BE(等)

我有以下表格...

Person         ( ID, Name, LicenseTypes )
DrivingLicense ( ID, Type, ExpDate )
LicenseTypes   ( ID, Type )
Vehicle        ( ID, Brand, VehicleType )
VehicleType    ( ID, VehicleType )

我想实现的是，如果一个人的驾照类型为“A”并且他拥有的车辆是汽车，他可能不会驾驶该车辆，因为该车辆需要“B”。

如果此人有多种驾驶执照类型，假设:A 和 B，对汽车的要求是 B，他可以驾驶车辆。

是否最好在 Vehicletype 表中添加一列，使用 Licensetype 和 Join 这些表？

或者我可以使用 MySQL 行/PHP 命令来比较这些值并使用某种语句吗？ IF value = ""and "", 但我认为这将是一个困惑的解决方案。

Answer 1

好的，这会很长。我希望您能大致了解我在这里所做的尝试；总而言之，我使用了 6 个表。此外，我没有使用您的表名(在我尝试我的东西时，问题和评论中有一些编辑)，抱歉。

下面的代码适用于例如sqlite3 我想我只使用了 ANSI SQL，所以它应该适用于所有东西。使用本地数据库的特殊性进行改进；例如，我没有放入自动递增 ID，并且只对文本使用 varchar，因为这是最大的公分母。

在每个表定义下面，我放入了一些虚拟数据加载语句以查看是否一切正常，我还放入了一些 select 语句以制作一个漂亮的列表。当你在 sqlite3 中运行它时，你会得到以下输出:

What person has which license?  
Karel        A             required to drive a motorcycle
Karel        B             required to drive a car       
John         AM            required to drive a scooter   
John         B             required to drive a car       

What vehicle types may be driven with which licenses?  
car                B           
scooter            AM          
scooter            A           
scooter            B           
motorcycle         A           

Which person may drive which vehicle given their license?  
Karel        Solex         scooter            A             required to drive a motorcycle
Karel        Vespa         scooter            A             required to drive a motorcycle
Karel        BMW K1200GT   motorcycle         A             required to drive a motorcycle
Karel        Ducati Monst  motorcycle         A             required to drive a motorcycle
Karel        Peugeot 307   car                B             required to drive a car       
Karel        Volvo V70     car                B             required to drive a car       
Karel        Solex         scooter            B             required to drive a car       
Karel        Vespa         scooter            B             required to drive a car       
John         Solex         scooter            AM            required to drive a scooter   
John         Vespa         scooter            AM            required to drive a scooter   
John         Peugeot 307   car                B             required to drive a car       
John         Volvo V70     car                B             required to drive a car       
John         Solex         scooter            B             required to drive a car       
John         Vespa         scooter            B             required to drive a car 

继续进入 SQL 代码。首先，您拥有他们持有的人员和执照。我使用典型的交叉表对此进行了建模； person 包含有关人员的信息，license 包含有关许可证类型的信息，person_license 是表示哪个人拥有哪些许可证的交叉表:

-- Known persons
create table person (
    person_id   integer not null unique primary key,
    person_name varchar(255) not null
);
insert into person (person_id, person_name) values (1, 'Karel');
insert into person (person_id, person_name) values (2, 'John');

-- Known license types
create table license (
    license_id   integer not null unique primary key,
    license_name varchar(3) not null unique,
    license_desc varchar(255)
);
insert into license (license_id, license_name, license_desc)
values (1, 'AM', 'required to drive a scooter');
insert into license (license_id, license_name, license_desc)
values (2, 'A' , 'required to drive a motorcycle');
insert into license (license_id, license_name, license_desc)
values (3, 'B' , 'required to drive a car');

-- What licenses do the persons hold (cross table so that 1 person may hold
-- multiple licenses. For example: Karel has A and B, John has AM and B.
-- NOTE: There is no expiry date here, you could stick it into this table when
-- you need it.
create table person_license (
    person_id  integer references person(person_id) not null,
    license_id integer references license(license_id) not null
);
insert into person_license (person_id, license_id) values (1, 2);
insert into person_license (person_id, license_id) values (1, 3);
insert into person_license (person_id, license_id) values (2, 1);
insert into person_license (person_id, license_id) values (2, 3);

-- Here is a listing to show what licenses a given person has.
select 'What person has which license?';
select person.person_name, license.license_name, license.license_desc
  from person, license, person_license
 where person_license.person_id = person.person_id
   and person_license.license_id = license.license_id;
select '';   

接下来是系统中的车辆和车辆类型。对于演示，我输入了三种类型；小型摩托车、摩托车和普通汽车。您可能希望将其更改为 cars-without-trailer 和 cars-with-trailer 等，具体取决于您需要支持的许可证类型。表vehicle_type当然是类型，vehicle是系统中所有车辆的表。它具有 1:N 关系，因此它会列出每种车辆的类型。

-- Vehicle types.
create table vehicle_type (
    vehicle_type_id   integer not null unique primary key,
    vehicle_type_name varchar(255) not null    
);
insert into vehicle_type (vehicle_type_id, vehicle_type_name)
values (1, 'car');
insert into vehicle_type (vehicle_type_id, vehicle_type_name)
values (2, 'scooter');
insert into vehicle_type (vehicle_type_id, vehicle_type_name)
values (3, 'motorcycle');

-- Known vehicles in our system (2 cars, 2 scooters, 2 motorbikes)
create table vehicle (
    vehicle_id      integer not null unique primary key,
    vehicle_name    varchar(255) not null,
    vehicle_type_id integer not null references vehicle_type(vehicle_type_id));
insert into vehicle (vehicle_id, vehicle_name, vehicle_type_id)
values (1, 'Peugeot 307', 1); 
insert into vehicle (vehicle_id, vehicle_name, vehicle_type_id)
values (2, 'Volvo V70', 1); 
insert into vehicle (vehicle_id, vehicle_name, vehicle_type_id)
values (3, 'Vespa', 2); 
insert into vehicle (vehicle_id, vehicle_name, vehicle_type_id)
values (4, 'Solex', 2); 
insert into vehicle (vehicle_id, vehicle_name, vehicle_type_id)
values (5, 'BMW K1200GT', 3); 
insert into vehicle (vehicle_id, vehicle_name, vehicle_type_id)
values (6, 'Ducati Monster', 3); 

然后是车辆类型和所需许可证之间的关系。我把它做成 N:N 关系，这样一个驾照就可以让你驾驶不止一种车型。在此演示中，汽车执照允许您驾驶汽车或踏板车；摩托车驾照允许您驾驶摩托车或踏板车，而踏板车驾照仅允许您驾驶踏板车。

-- What license do you need for what type of a vehicle?
-- A scooter license allows you only to drive a scooter.
-- A motorcycle license allows you to drive a motorbike and a scooter.
-- A car license allows you to drive a car and a scooter.
create table vehicle_type_license (
    vehicle_type_id integer not null references vehicle_type(vehicle_type_id),
    license_id      integer not null references license(license_id)
);
-- Car requires B
insert into vehicle_type_license (vehicle_type_id, license_id) values (1, 3);
-- Scoorter requires AM, A or B
insert into vehicle_type_license (vehicle_type_id, license_id) values (2, 1);
insert into vehicle_type_license (vehicle_type_id, license_id) values (2, 2);
insert into vehicle_type_license (vehicle_type_id, license_id) values (2, 3);
-- Motorcycle requires A
insert into vehicle_type_license (vehicle_type_id, license_id) values (3, 2);
-- And a listing to show it
select 'What vehicle types may be driven with which licenses?';
select vehicle_type.vehicle_type_name, license.license_name
  from vehicle_type, license, vehicle_type_license
 where vehicle_type.vehicle_type_id = vehicle_type_license.vehicle_type_id
   and license.license_id = vehicle_type_license.license_id;
select '';

最后..这是谁可以驾驶什么车辆的结果:

-- Finally...
-- Let us see what vehicles the persons may drive.
select 'Which person may drive which vehicle given their license?';
select person.person_name, vehicle.vehicle_name,
       vehicle_type.vehicle_type_name,
       license.license_name, license.license_desc
  from person, vehicle, vehicle_type,
       license, person_license, vehicle_type_license
 where vehicle.vehicle_type_id = vehicle_type.vehicle_type_id
   and license.license_id = person_license.license_id
   and person.person_id = person_license.person_id
   and vehicle_type_license.vehicle_type_id = vehicle_type.vehicle_type_id
   and vehicle_type_license.license_id = person_license.license_id
;