Mysql-5

我的數據庫設計的結構？

  • February 28, 2021

我一直在使用 SQL 開發 C# Bus System 應用程序，我需要一些建議。我不知道在這裡詢問是否正確，但我基本上需要有人分析我擁有的 .SQL 文件，我在其中設計了我的數據庫模式。主要問題是，這是否以正確的方式設計（什麼是正確的方式？），是否將 UUID 作為每個表的主鍵？它應該只用於機構表嗎？等等

我不知道有沒有什麼地方我可以花他們一個小時的時間來分析它….

SQL 文件長 430 行，所以我已將其粘貼在下面，如果這不是正確的做法，我深表歉意！

謝謝^.^

-- version 5.0.2
-- https://www.phpmyadmin.net/
--
-- Host: edu-route-test-db.cqobdbj3xwim.us-east-1.rds.amazonaws.com:3306
-- Generation Time: Feb 28, 2021 at 01:45 PM
-- Server version: 8.0.20
-- PHP Version: 7.3.21

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- Database: `EduRouteDB`
--

-- --------------------------------------------------------

--
-- Table structure for table `addresses`
--

DROP TABLE IF EXISTS `addresses`;
CREATE TABLE IF NOT EXISTS `addresses` (
 `AddressId` binary(16) NOT NULL DEFAULT (uuid_to_bin(uuid())),
 `BuildingName` varchar(256) NOT NULL,
 `StreetName` varchar(256) NOT NULL,
 `Town` varchar(256) NOT NULL,
 `County` varchar(256) NOT NULL,
 `PostCode` varchar(8) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
 `Country` varchar(256) NOT NULL,
 PRIMARY KEY (`AddressId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- --------------------------------------------------------

--
-- Table structure for table `bookings`
--

DROP TABLE IF EXISTS `bookings`;
CREATE TABLE IF NOT EXISTS `bookings` (
 `BookingId` binary(16) NOT NULL,
 `StudentId` binary(16) NOT NULL,
 `StartDate` date NOT NULL,
 `EndDate` date NOT NULL,
 `TimeBooked` time NOT NULL,
 `RouteId` binary(16) NOT NULL,
 `StopId` binary(16) NOT NULL,
 `UserId` binary(16) NOT NULL,
 `InstitutionId` binary(16) NOT NULL,
 PRIMARY KEY (`BookingId`),
 KEY `StudentId_Bookings_FK` (`StudentId`),
 KEY `RouteId_Bookings_FK` (`RouteId`),
 KEY `StopId_Bookings_FK` (`StopId`),
 KEY `InstitutionId_Bookings_FK` (`InstitutionId`),
 KEY `UserId_Bookings_FK` (`UserId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- --------------------------------------------------------

--
-- Table structure for table `buses`
--

DROP TABLE IF EXISTS `buses`;
CREATE TABLE IF NOT EXISTS `buses` (
 `BusId` binary(16) NOT NULL DEFAULT (uuid_to_bin(uuid())),
 `BusName` varchar(256) NOT NULL,
 `VehicleRegistrationNumber` varchar(10) NOT NULL,
 `Capacity` int NOT NULL,
 `InstitutionId` binary(16) NOT NULL DEFAULT (uuid_to_bin(uuid())),
 PRIMARY KEY (`BusId`),
 KEY `InstitutionId_Buses_FK` (`InstitutionId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- --------------------------------------------------------

--
-- Table structure for table `buses_routes`
--

DROP TABLE IF EXISTS `buses_routes`;
CREATE TABLE IF NOT EXISTS `buses_routes` (
 `BusId` binary(16) NOT NULL,
 `RouteId` binary(16) NOT NULL,
 `InstitutionId` binary(16) NOT NULL,
 KEY `BusId_BusesRoutes_FK` (`BusId`),
 KEY `RouteId_BusesRoutes_FK` (`RouteId`),
 KEY `InstitutionId_BusesRoutes_FK` (`InstitutionId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- --------------------------------------------------------

--
-- Table structure for table `drivers`
--

DROP TABLE IF EXISTS `drivers`;
CREATE TABLE IF NOT EXISTS `drivers` (
 `DriverId` binary(16) NOT NULL DEFAULT (uuid_to_bin(uuid())),
 `DriverName` varchar(256) NOT NULL,
 `BusId` binary(16) NOT NULL DEFAULT (uuid_to_bin(uuid())),
 `InstitutionId` binary(16) NOT NULL DEFAULT (uuid_to_bin(uuid())),
 `UserId` binary(16) NOT NULL DEFAULT (uuid_to_bin(uuid())),
 PRIMARY KEY (`DriverId`),
 KEY `BusId_Drivers_FK` (`BusId`),
 KEY `InstitutionId_Drivers_FK` (`InstitutionId`),
 KEY `UserId_Drivers_FK` (`UserId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- --------------------------------------------------------

--
-- Table structure for table `institutions`
--

DROP TABLE IF EXISTS `institutions`;
CREATE TABLE IF NOT EXISTS `institutions` (
 `InstitutionId` binary(16) NOT NULL DEFAULT (uuid_to_bin(uuid())),
 `InstituteName` varchar(256) DEFAULT NULL,
 `InstituteLogoFullPath` varchar(256) DEFAULT NULL,
 `InstituteJoinDate` date DEFAULT NULL,
 `IsActive` tinyint(1) DEFAULT NULL,
 `PrincipalName` varchar(256) DEFAULT NULL,
 `AddressId` binary(16) NOT NULL DEFAULT (uuid_to_bin(uuid())),
 PRIMARY KEY (`InstitutionId`),
 KEY `AddressId_FK` (`AddressId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- --------------------------------------------------------

--
-- Table structure for table `parents`
--

DROP TABLE IF EXISTS `parents`;
CREATE TABLE IF NOT EXISTS `parents` (
 `ParentId` binary(16) NOT NULL DEFAULT (uuid_to_bin(uuid())),
 `FirstName` varchar(256) NOT NULL,
 `MiddleName` varchar(256) NOT NULL,
 `LastName` varchar(256) NOT NULL,
 `InstitutionId` binary(16) NOT NULL DEFAULT (uuid_to_bin(uuid())),
 `UserId` binary(16) NOT NULL DEFAULT (uuid_to_bin(uuid())),
 PRIMARY KEY (`ParentId`),
 KEY `InstitutionId_Parents_FK` (`InstitutionId`),
 KEY `UserId_Parents_FK` (`UserId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- --------------------------------------------------------

--
-- Table structure for table `permissions`
--

DROP TABLE IF EXISTS `permissions`;
CREATE TABLE IF NOT EXISTS `permissions` (
 `PermissionId` binary(16) NOT NULL DEFAULT (uuid_to_bin(uuid())),
 `PermissionCategory` varchar(256) NOT NULL,
 `PermissionName` varchar(256) NOT NULL,
 PRIMARY KEY (`PermissionId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- --------------------------------------------------------

--
-- Table structure for table `roles`
--

DROP TABLE IF EXISTS `roles`;
CREATE TABLE IF NOT EXISTS `roles` (
 `RoleId` binary(16) NOT NULL DEFAULT (uuid_to_bin(uuid())),
 `RoleType` varchar(256) NOT NULL,
 `InstitutionId` binary(16) NOT NULL,
 PRIMARY KEY (`RoleId`),
 KEY `InstitutionId_Roles_FK` (`InstitutionId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- --------------------------------------------------------

--
-- Table structure for table `roles_permissions`
--

DROP TABLE IF EXISTS `roles_permissions`;
CREATE TABLE IF NOT EXISTS `roles_permissions` (
 `RoleId` binary(16) NOT NULL DEFAULT (uuid_to_bin(uuid())),
 `PermissionId` binary(16) NOT NULL DEFAULT (uuid_to_bin(uuid())),
 `InstitutionId` binary(16) NOT NULL DEFAULT (uuid_to_bin(uuid())),
 KEY `RoleId_Roles_Permissions_FK` (`RoleId`),
 KEY `PermissionId_Roles_Permissions_FK` (`PermissionId`),
 KEY `InstitutionId_Roles_Permissions_FK` (`InstitutionId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- --------------------------------------------------------

--
-- Table structure for table `routes`
--

DROP TABLE IF EXISTS `routes`;
CREATE TABLE IF NOT EXISTS `routes` (
 `RouteId` binary(16) NOT NULL DEFAULT (uuid_to_bin(uuid())),
 `RouteName` varchar(256) NOT NULL,
 `Mon` tinyint(1) NOT NULL DEFAULT '0',
 `Tue` tinyint(1) NOT NULL DEFAULT '0',
 `Wed` tinyint(1) NOT NULL DEFAULT '0',
 `Thu` tinyint(1) NOT NULL DEFAULT '0',
 `Fri` tinyint(1) NOT NULL DEFAULT '0',
 `Sat` tinyint(1) NOT NULL DEFAULT '0',
 `Sun` tinyint(1) NOT NULL DEFAULT '0',
 `StartDate` date NOT NULL,
 `EndDate` date NOT NULL,
 `StartTime` time NOT NULL,
 `EndTime` time NOT NULL,
 `CurrentNumberOfBookings` int NOT NULL,
 `InstitutionId` binary(16) NOT NULL DEFAULT (uuid_to_bin(uuid())),
 PRIMARY KEY (`RouteId`),
 KEY `InstitutionId_Routes_FK` (`InstitutionId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- --------------------------------------------------------

--
-- Table structure for table `routes_stops`
--

DROP TABLE IF EXISTS `routes_stops`;
CREATE TABLE IF NOT EXISTS `routes_stops` (
 `RouteId` binary(16) NOT NULL DEFAULT (uuid_to_bin(uuid())),
 `StopId` binary(16) NOT NULL DEFAULT (uuid_to_bin(uuid())),
 `Time` time NOT NULL,
 `InstitutionId` binary(16) NOT NULL DEFAULT (uuid_to_bin(uuid())),
 KEY `RouteId_RoutesStops_FK` (`RouteId`),
 KEY `StopId_RoutesStops_FK` (`StopId`),
 KEY `InstitutionId_RoutesStops_FK` (`InstitutionId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- --------------------------------------------------------

--
-- Table structure for table `staff`
--

DROP TABLE IF EXISTS `staff`;
CREATE TABLE IF NOT EXISTS `staff` (
 `StaffId` binary(16) NOT NULL DEFAULT (uuid_to_bin(uuid())),
 `InstitutionId` binary(16) NOT NULL DEFAULT (uuid_to_bin(uuid())),
 `FirstName` varchar(256) NOT NULL,
 `MiddleName` varchar(256) NOT NULL,
 `LastName` varchar(256) NOT NULL,
 `UserId` binary(16) NOT NULL DEFAULT (uuid_to_bin(uuid())),
 KEY `UserId_FK` (`UserId`),
 KEY `InstitutionId_Staff_FK` (`InstitutionId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- --------------------------------------------------------

--
-- Table structure for table `stops`
--

DROP TABLE IF EXISTS `stops`;
CREATE TABLE IF NOT EXISTS `stops` (
 `StopId` binary(16) NOT NULL DEFAULT (uuid_to_bin(uuid())),
 `StopName` varchar(256) NOT NULL,
 `Longitude` decimal(9,6) NOT NULL,
 `Latitude` decimal(9,6) NOT NULL,
 `AddressId` binary(16) NOT NULL DEFAULT (uuid_to_bin(uuid())),
 `InstitutionId` binary(16) NOT NULL DEFAULT (uuid_to_bin(uuid())),
 PRIMARY KEY (`StopId`),
 KEY `AddressId_Stops_FK` (`AddressId`),
 KEY `InstitutionId_Stops_FK` (`InstitutionId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- --------------------------------------------------------

--
-- Table structure for table `students`
--

DROP TABLE IF EXISTS `students`;
CREATE TABLE IF NOT EXISTS `students` (
 `StudentId` binary(16) NOT NULL DEFAULT (uuid_to_bin(uuid())),
 `FirstName` varchar(256) NOT NULL,
 `MiddleName` varchar(256) NOT NULL,
 `LastName` varchar(256) NOT NULL,
 `DateOfBirth` date NOT NULL,
 `InstitutionId` binary(16) NOT NULL DEFAULT (uuid_to_bin(uuid())),
 PRIMARY KEY (`StudentId`),
 KEY `InstitutionId_Student_FK` (`InstitutionId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- --------------------------------------------------------

--
-- Table structure for table `students_parents`
--

DROP TABLE IF EXISTS `students_parents`;
CREATE TABLE IF NOT EXISTS `students_parents` (
 `StudentId` binary(16) NOT NULL DEFAULT (uuid_to_bin(uuid())),
 `ParentId` binary(16) NOT NULL DEFAULT (uuid_to_bin(uuid())),
 `InstitutionId` binary(16) NOT NULL DEFAULT (uuid_to_bin(uuid())),
 KEY `StudentId_StudentParent_FK` (`StudentId`),
 KEY `ParentId_StudentParent_FK` (`ParentId`),
 KEY `InstitutionId_StudentParent_FK` (`InstitutionId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- --------------------------------------------------------

--
-- Table structure for table `users`
--

DROP TABLE IF EXISTS `users`;
CREATE TABLE IF NOT EXISTS `users` (
 `UserId` binary(16) NOT NULL DEFAULT (uuid_to_bin(uuid())),
 `Email` varchar(256) DEFAULT NULL,
 `Password` char(60) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
 `InstitutionId` binary(16) NOT NULL DEFAULT (uuid_to_bin(uuid())),
 `RoleId` binary(16) NOT NULL DEFAULT (uuid_to_bin(uuid())),
 PRIMARY KEY (`UserId`),
 KEY `InstitutionId_FK` (`InstitutionId`),
 KEY `RoleId_Users_FK` (`RoleId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Constraints for dumped tables
--

--
-- Constraints for table `bookings`
--
ALTER TABLE `bookings`
 ADD CONSTRAINT `InstitutionId_Bookings_FK` FOREIGN KEY (`InstitutionId`) REFERENCES `institutions` (`InstitutionId`) ON DELETE CASCADE ON UPDATE CASCADE,
 ADD CONSTRAINT `RouteId_Bookings_FK` FOREIGN KEY (`RouteId`) REFERENCES `routes_stops` (`RouteId`) ON DELETE CASCADE ON UPDATE CASCADE,
 ADD CONSTRAINT `StopId_Bookings_FK` FOREIGN KEY (`StopId`) REFERENCES `routes_stops` (`StopId`) ON DELETE CASCADE ON UPDATE CASCADE,
 ADD CONSTRAINT `StudentId_Bookings_FK` FOREIGN KEY (`StudentId`) REFERENCES `students` (`StudentId`) ON DELETE CASCADE ON UPDATE CASCADE,
 ADD CONSTRAINT `UserId_Bookings_FK` FOREIGN KEY (`UserId`) REFERENCES `users` (`UserId`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `buses`
--
ALTER TABLE `buses`
 ADD CONSTRAINT `InstitutionId_Buses_FK` FOREIGN KEY (`InstitutionId`) REFERENCES `institutions` (`InstitutionId`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `buses_routes`
--
ALTER TABLE `buses_routes`
 ADD CONSTRAINT `BusId_BusesRoutes_FK` FOREIGN KEY (`BusId`) REFERENCES `buses` (`BusId`) ON DELETE CASCADE ON UPDATE CASCADE,
 ADD CONSTRAINT `InstitutionId_BusesRoutes_FK` FOREIGN KEY (`InstitutionId`) REFERENCES `institutions` (`InstitutionId`) ON DELETE CASCADE ON UPDATE CASCADE,
 ADD CONSTRAINT `RouteId_BusesRoutes_FK` FOREIGN KEY (`RouteId`) REFERENCES `routes` (`RouteId`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `drivers`
--
ALTER TABLE `drivers`
 ADD CONSTRAINT `BusId_Drivers_FK` FOREIGN KEY (`BusId`) REFERENCES `buses` (`BusId`) ON DELETE CASCADE ON UPDATE CASCADE,
 ADD CONSTRAINT `InstitutionId_Drivers_FK` FOREIGN KEY (`InstitutionId`) REFERENCES `institutions` (`InstitutionId`) ON DELETE CASCADE ON UPDATE CASCADE,
 ADD CONSTRAINT `UserId_Drivers_FK` FOREIGN KEY (`UserId`) REFERENCES `users` (`UserId`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `institutions`
--
ALTER TABLE `institutions`
 ADD CONSTRAINT `AddressId_FK` FOREIGN KEY (`AddressId`) REFERENCES `addresses` (`AddressId`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `parents`
--
ALTER TABLE `parents`
 ADD CONSTRAINT `InstitutionId_Parents_FK` FOREIGN KEY (`InstitutionId`) REFERENCES `institutions` (`InstitutionId`) ON DELETE CASCADE ON UPDATE CASCADE,
 ADD CONSTRAINT `UserId_Parents_FK` FOREIGN KEY (`UserId`) REFERENCES `users` (`UserId`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `roles`
--
ALTER TABLE `roles`
 ADD CONSTRAINT `InstitutionId_Roles_FK` FOREIGN KEY (`InstitutionId`) REFERENCES `institutions` (`InstitutionId`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `roles_permissions`
--
ALTER TABLE `roles_permissions`
 ADD CONSTRAINT `InstitutionId_Roles_Permissions_FK` FOREIGN KEY (`InstitutionId`) REFERENCES `institutions` (`InstitutionId`) ON DELETE CASCADE ON UPDATE CASCADE,
 ADD CONSTRAINT `PermissionId_Roles_Permissions_FK` FOREIGN KEY (`PermissionId`) REFERENCES `permissions` (`PermissionId`) ON DELETE CASCADE ON UPDATE CASCADE,
 ADD CONSTRAINT `RoleId_Roles_Permissions_FK` FOREIGN KEY (`RoleId`) REFERENCES `roles` (`RoleId`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `routes`
--
ALTER TABLE `routes`
 ADD CONSTRAINT `InstitutionId_Routes_FK` FOREIGN KEY (`InstitutionId`) REFERENCES `institutions` (`InstitutionId`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `routes_stops`
--
ALTER TABLE `routes_stops`
 ADD CONSTRAINT `InstitutionId_RoutesStops_FK` FOREIGN KEY (`InstitutionId`) REFERENCES `institutions` (`InstitutionId`) ON DELETE CASCADE ON UPDATE CASCADE,
 ADD CONSTRAINT `RouteId_RoutesStops_FK` FOREIGN KEY (`RouteId`) REFERENCES `routes` (`RouteId`) ON DELETE CASCADE ON UPDATE CASCADE,
 ADD CONSTRAINT `StopId_RoutesStops_FK` FOREIGN KEY (`StopId`) REFERENCES `stops` (`StopId`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `staff`
--
ALTER TABLE `staff`
 ADD CONSTRAINT `InstitutionId_Staff_FK` FOREIGN KEY (`InstitutionId`) REFERENCES `institutions` (`InstitutionId`) ON DELETE CASCADE ON UPDATE CASCADE,
 ADD CONSTRAINT `UserId_FK` FOREIGN KEY (`UserId`) REFERENCES `users` (`UserId`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `stops`
--
ALTER TABLE `stops`
 ADD CONSTRAINT `AddressId_Stops_FK` FOREIGN KEY (`AddressId`) REFERENCES `addresses` (`AddressId`) ON DELETE CASCADE ON UPDATE CASCADE,
 ADD CONSTRAINT `InstitutionId_Stops_FK` FOREIGN KEY (`InstitutionId`) REFERENCES `institutions` (`InstitutionId`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `students`
--
ALTER TABLE `students`
 ADD CONSTRAINT `InstitutionId_Student_FK` FOREIGN KEY (`InstitutionId`) REFERENCES `institutions` (`InstitutionId`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `students_parents`
--
ALTER TABLE `students_parents`
 ADD CONSTRAINT `InstitutionId_StudentParent_FK` FOREIGN KEY (`InstitutionId`) REFERENCES `institutions` (`InstitutionId`) ON DELETE CASCADE ON UPDATE CASCADE,
 ADD CONSTRAINT `ParentId_StudentParent_FK` FOREIGN KEY (`ParentId`) REFERENCES `parents` (`ParentId`) ON DELETE CASCADE ON UPDATE CASCADE,
 ADD CONSTRAINT `StudentId_StudentParent_FK` FOREIGN KEY (`StudentId`) REFERENCES `students` (`StudentId`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `users`
--
ALTER TABLE `users`
 ADD CONSTRAINT `InstitutionId_FK` FOREIGN KEY (`InstitutionId`) REFERENCES `institutions` (`InstitutionId`) ON DELETE CASCADE ON UPDATE CASCADE,
 ADD CONSTRAINT `RoleId_Users_FK` FOREIGN KEY (`RoleId`) REFERENCES `roles` (`RoleId`) ON DELETE CASCADE ON UPDATE CASCADE;
COMMIT;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

我快速掃描了生成架構的 SQL 文件。我認為你有一個良好的開端，其中很多是有道理的。這些是對我來說很突出的事情，您可能需要考慮更多（或者根據您的領域知識，有些事情可能是有意義的）：

  1. BusId將列儲存在drivers表中會限制您在給定時間只能為每輛巴士定義一個驅動程序。我希望這更多route或booking依賴，因為從理論上講，不同的司機可以在不同的日子在不同的路線上使用公共汽車。或者，如果driver給定的正常人bus生病了會發生什麼？……那麼不同的driver人甚至可以接管。另一個bus_drivers具有多對多關係的表在這裡可能會有所幫助。
  2. 我認為您不需要將其儲存InstitutionId在很多表上，因為它似乎是多餘的，因為您可以引用地訪問它。例如，該parents表被引用students_parents，也引用students. 表和表中InstitutionId已經存在，students因此是多餘的。parents``students_parents
  3. 該routes表具有星期一到星期五的列（例如Mon、Tue等）。我認為這是一種表示在哪一天routes執行的方法。這沒關係，但如果您將此資訊儲存在一個schedules表中，可能會更好，並為您提供更大的靈活性，這可能是由實際日期驅動的。再次，您可能會遇到多種情況route，例如通常安排在星期一的情況，但即將到來的星期一是假期，因此這一次實際上當天不活動。

除了上述建議之外，要直接回答您關於主鍵的問題，是的，您應該始終嘗試在每個表上創建一個主鍵（除了臨時表的極少數情況，但這與您的架構無關）。它們是否應該是UUID數據類型取決於您。儘管通常這僅在您的數據庫系統分佈在多個消費者之間執行的情況下才重要，這些消費者將在您的集中式數據庫之外同時並行生成數據您需要一種最終將這些數據整合到一個集中式數據庫中的方法，以確保全球唯一性。例如，在允許使用者在其設備離線時創建數據並將其儲存在設備上的本地數據庫中直到他們重新線上然後將其同步到主集中式數據庫的移動應用程序的上下文中。即使在這種情況下，也有一些方法可以使用不同的數據類型，例如整數標識列作為集中式數據庫上的主鍵。

如果您的方案不涉及上述案例，那麼我強烈建議使用基於整數的 auto_increment 標識列作為每個表中的主鍵。它需要更少的數據儲存，保證唯一性（因為只有一個數據庫在使用），自動生成，更重要的是更有效地JOIN在表之間建立索引。

引用自：https://dba.stackexchange.com/questions/286173

相關問答

Mysql

如何在 MySQL 中編寫可以從觸發器和儲存函式呼叫的信號函式？

  • December 28, 2021
檢視問答
Mysql

MyISAM 表的性能設置 - 將所有內容保存在記憶體中

  • December 21, 2021
檢視問答
Mysql

MySQL 5 中的計算/生成列

  • November 19, 2021
檢視問答
Mysql

用不同的名稱恢復mysql數據庫

  • September 28, 2021
檢視問答
Mysql

即使有索引，MySQL 查詢也很慢

  • September 7, 2021
檢視問答
Mysql

有沒有辦法在共享主機中找到 my.cnf？

  • September 1, 2021
檢視問答