3

I've been developing a C# Bus System app using SQL, and I need some advice. I don't know if asking on here is the right thing to do, but I essentially need someone to analyse the .SQL file I have, where I have designed my database schema. Primary questions are really, is this designed in the right way (what is the right way?), are having UUIDs as primary keys for EVERY table necessary? Should it just be for the institution table? etc.

I don't know if there is anywhere I can go to pay an hour of their time to analyse it....

The SQL file is 430 lines long so, i've pasted it below, I apologise if this is not the right thing to do on here!

Thanks ^.^

-- 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 /;
Suzan Aydın
  • 133
  • 3
  • 2
    When you mentioned C# and tagged the question as sql-server I assumed you were using Microsoft SQL Server, but by your code it looks like you're using MySQL for the database system. Please update your question tags with the specific database system and version, as those are generally required (and you can remove the sql-server tag if your question isn't about Microsoft SQL Server). – J.D. Feb 28 '21 at 14:18

1 Answers1

1

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.

J.D.
  • 37,483
  • 8
  • 54
  • 121
  • Thanks J.D for the thorough answer - this definitely helps and I’m glad I wasn’t too far off! There is a possibility that if the internet is down, I’d like users to still be able to store their information for later uploading (from a csv or json perhaps). Is there a huge performance difference with using UUID versus an int that’s auto incremented? Also, for organisation IDs... or company IDs... do people tend to use UUIDs? Or do they use something as simple as int autoincrement? Thanks for the advice on the tables, I’ll also look into that :) – Suzan Aydın Feb 28 '21 at 21:47
  • @SuzanAydın Absolutely, no problem! It depends on your definition of huge and what's acceptable in your system, and it really depends on the exact queries you're running, e.g. if typically your queries only get a few number of rows that result in index seeks vs index scans on the UUID keys. I've definitely seen cases where UUID is up to four times as slow as an INT but I wouldn't expect them to be 100x slower. Though another factor in performance of UUID fields is their randomness in values tends to result in index fragmentation much more easily than on an INT field. – J.D. Feb 28 '21 at 21:53
  • 1
    The thing a lot of developers forget, even in the case of distributed parallel data generation that later needs to be consolidated, is that UUID isn't guaranteed to be globally unique 100% of the time, even though it usually is unique. But to be a good developer, one should still implement collision handling during the data consolidation process. My opinion is if we're already going to the extent of implementing collision handling logic, then it's not much different to implement such logic when the colliding data type is an INT or any data type for that matter. So I personally see no... – J.D. Feb 28 '21 at 21:57
  • 1
    ...benefit in the UUID data type in most cases, even with distributed parallel data generation that needs to be consolidated. The one case where I do find it has a use, is it obfuscates the order in which that record was generated, which is useful in certain use cases / contexts where security is a factor, to prevent someone from guessing the ID of another record. But I digress since this is a little outside the scope of your question. :) – J.D. Feb 28 '21 at 21:58
  • Thanks for the explanation J.D :). I'm a bit of a noob when it comes to DBs... i'm a developer by heart where I've only touched on SQL here and there. I can design a DB to the best of my ability with relational knowledge but when you mentioned collision handling you lost me a bit... Do you have a good example of collision handling? I'm happy to modify it all to be an int as the ID if necessary, but I'm not too sure how to handle collisions (i'm assuming this is when one client on another machine tries to modify a row and someone else on another machine tries to do the same).. – Suzan Aydın Feb 28 '21 at 22:12
  • 1
    @SuzanAydın No worries, collision handling in this context means key collisions, e.g. if in offline mode two UUIDs are generated with the same values in two different offline systems (by two different users). When those same values for the primary key are inserted into the consolidated database (once back online), an error will be thrown since the primary key of a table needs to be unique. UUIDs don't normally collide, but it does definitely happen, I've experienced it plenty. Unfortunately a quick Googly hasn't turned up any literature I think would be sufficient, but... – J.D. Feb 28 '21 at 22:23
  • 1
    ...usually the solution to collision handling is to detect and catch collision errors during data consolidation and just re-generate a new key. The odds of a second collision happening with UUID is very unlikely, and becomes less likely on each subsequent collision, should one occur. In the case of an auto_increment INT identity column, you're instead letting the centralized database generate the primary key values for you on INSERT, so you'd have to get the newly generated ID after INSERT and update the local database entity's keys accordingly, a bi-directional fix. – J.D. Feb 28 '21 at 22:24
  • Thanks J.D. I've seen retrieving the newly inserted ID and I have seen issues with this, basically with what you're talking about, where one online system can insert into the same table as another online system is inserting, and the first system tries to retrieve the newly inserted ID but retrieves the one created by the second system, if that makes sense. I don't know if i've found a solution to that. I suppose in the code (in my case, the C# code triggering an insert), if the insert fails with "UUID already present" or some such, I can just try to do the insert again until it succeeds? – Suzan Aydın Mar 03 '21 at 18:59
  • 1
    @SuzanAydin Typically database systems have built in mechanisms to track the INSERT to the scope of the connection or call that created the INSERT to prevent concurrency issues. For example in Microsoft SQL Server they have a function called SCOPE_IDENTITY() for this very case to ensure you only get the most recent identity value your INSERT created. Unfortunately I'm not as familiar with MySQL and wouldn't know what the mechanism is called there. – J.D. Mar 03 '21 at 20:59
  • 1
    If you do stick with a UUID though, then yes for collision issues you basically just need to ensure you catch the exception that's thrown for a duplicate key trying to be inserted, and then re-generate a new UUID and retry, rinse and repeat on any subsequent collisions. – J.D. Mar 03 '21 at 21:00
  • 1
    Thanks JD you've been super helpful :) – Suzan Aydın Mar 08 '21 at 23:00
  • @SuzanAydın No problem! Glad to be of help! – J.D. Mar 09 '21 at 00:02
  • I was thinking J.D, how would you integrate a schedules table? I'm thinking since it's for a school, it'll need to have the potential to take into account holiday periods and exclude them for certain routes. Do you have an idea? Trying to formulate it in a table – Suzan Aydın Mar 10 '21 at 20:29
  • 1
    @SuzanAydin I'd have to think about it a little bit, but one way to do it, speaking at a high level, is it would have the fields ScheduleId, RouteId, ScheduleDateTime (possibly the InstitutionId too if the same Route can be used by multiple Institutions but need different Schedules). Every row in the table would be a single date & time of that particular Route's Schedule. For example, if Route123 had a schedule for only Mondays at 3PM, there'd be a row in there for every valid Monday that Route needs scheduled. E.g. if the school year is only 40 weeks long, then you'd have... – J.D. Mar 11 '21 at 13:03
  • 1
    ...40 rows of dates (for Mondays) at 3 PM in the ScheduleDateTime column. Doing it this was may result in a larger table by number of rows, and forces the work of generating the schedules / determining which dates to skip every year (or however often you can foreseeably determine) downstream in your application code. So this kind of implementation may or may not be good. Alternatively you can have a separate DatesDimension table which stores the calendar years for the next X number of years (I usually generate 100 years) and has all the holidays etc built into it. There's SQL scripts... – J.D. Mar 11 '21 at 13:05
  • 1
    ...out there that'll generate the DatesDimension table for you. (Unfortunately I'm only familiar with Microsoft SQL Server so this is what I'd use but you'd need a MySQL equivalent.) They're pretty cool though, they have all the dates, fields to specify different holidays, weekends, and different formats of the dates such as which week of the year a particular date is in, or the numerical representation of a given date etc. So if you generated one of these tables you could then have a... – J.D. Mar 11 '21 at 13:11
  • 1
    ...more generic Schedules table that specified which day of the week a particular Route's Schedule is for (e.g. Monday, Wednesday, Friday) and you'd only have to join it to your DatesDimension table whenever you wanted to get all the Mondays for a given year, for example. Then you can see which Mondays are holidays and filter them out, etc. Even in the generic Schedule table above, I'd still store a separate row per day, so in my same example you'd have 3 rows total for Monday, Wednesday, Friday for a particular Route (but that's still better than my previous suggestion) and you... – J.D. Mar 11 '21 at 13:13
  • 1
    ...wouldn't have to worry about maintaining the schedules every year (or at least not as frequently) in your application because it would be based off your DatesDimension table that you'd only have to generate once and can be generated for any number of years needed – J.D. Mar 11 '21 at 13:16
  • Thanks for this J.D! Curious though, different schools have different "holidays" (excluding public holidays) i.e. they might have holiday club between 25th March to 13th April (buses may not operate between this time). These are obviously not public holidays, and this will differ between schools. Should I create a dates dimension table and a separate holiday table with institution ID as a foreign key? Sorry for all the qs! – Suzan Aydın Mar 12 '21 at 23:10
  • Maybe a holidays table with columns HolidayId, DayId (from DatesDimension) and InstitutionId – Suzan Aydın Mar 12 '21 at 23:15
  • 1
    @SuzanAydin Yea I think that sounds reasonable. A dates dimension table is good for global generic stuff around dates (and should be part a more generic shared schema if you plan to have other applications, because it could be useful for those applications as well), but for the individual custom holidays for the schools, a separate table might be good idea that stores the dates with the InstitutionId, and can always be re-joined to your dates dimension table for additional information about those days as needed (like what day of the week it is, etc). – J.D. Mar 13 '21 at 00:23
  • 1
    Thanks again J.D xD – Suzan Aydın Mar 13 '21 at 18:38