主页 / dba / 问题 / 286173
Accepted
Suzan Aydın
Asked: 2021-03-01 05:57:28 +0800 CST

我的数据库设计的结构？

  • 772

我一直在使用 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_general_ci NOT NULL,
  `Country` varchar(256) NOT NULL,
  PRIMARY KEY (`AddressId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_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_general_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_general_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_general_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_general_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_general_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_general_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_general_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_general_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_general_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_general_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_general_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_general_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_general_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_general_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_general_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_general_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 */;
mysql-5

1 个回答

  1. Best Answer

    I quickly scanned your SQL file that generates your schema. I think you're off to a good start, a lot of it makes sense. These are the things that stood out to me that you might want to think about a little more (or some may make sense as is, based on your domain knowledge):

    1. Storing the BusId column in drivers table limits you to only being able to define one driver per bus at a given time. I would expect this to be more route or booking dependent, as theoretically buses can be used by different drivers across different routes over different days. Or what happens if the normal driver for a given bus is out sick?...then a different driver could even take over. Another table of bus_drivers with a many-to-many relationship might be helpful here.

    2. I don't think you have a need to store the InstitutionId on a lot of tables, as it appears to be redundant since you're able to access it referentially. For example, the parents table is referenced by students_parents which also references students. The InstitutionId already exists in students and therefore is redundant in the parents table and students_parents table.

    3. The routes table has the Monday through Friday columns (e.g. Mon, Tue, etc). I assume this is a way to denote which routes operate on which days. This is ok, but might be better and give you more flexibility if you stored this information in a schedules table instead, that is perhaps driven by actual dates. Since again you can run into multiple situations like a route that is normally scheduled for Mondays but this coming Monday is a holiday so it actually isn't active that day this one time.

    Outside of the above suggestions, to directly answer your questions on primary keys, yes you should always try to create a primary key on every table (except rare cases with staging tables, but that's not relevant to your schema here). Should they be the UUID data type is up to you. Though generally that's only important in cases where your database system runs distributed across multiple consumers who will be generating data simultaneously in parallel outside of your centralized database and you need a way to eventually consolidate that data into one centralized database, as a way to ensure global uniqueness. For example, in the context of a mobile application that allows users to create data while their device is offline and stores it in a local database on the device until they're back online and then synchronizes it to the main centralized database. Even in that scenario there are ways to use a different data type like an integer identity column as your primary key on the centralized database.

    If your scenario doesn't involve the above use case, then I'd strongly recommend using an integer based auto_increment identity column as your primary key in each table. It'll require less data storage, guarantee uniqueness (since only one database is at play), auto generate itself, and more importantly be more efficient to index and JOIN on between your tables.

    • 1

相关问题