Mysql - Two primary keys in one table -(auto increment and one is to one primary key - subtype)

Question

Im having trouble relating tables from one another.
Here is what I want to create based on my understanding.

I have an organization that can request a service, he can select from different services (Service1, Service2, OtherServices) The Service1-2 are fixed and they have their own specific column names (this means that when an organization request Service1 or 2, they have to fill up a form), while the OtherServices are the services with no forms. So if the adminadds a new service, it will automatically be a service with no forms, therefore, will reflect on the OtherServices table.

All TrackingNo is auto-increment The ServiceID from each Services are 1 is 1-to-1 with Table Service and the RequestID from every services are foreign key to table Request_Service

This is what my table looks like

Answer 1

In this situation I would go for document centric storage of your services instead of forcing them into a relational data structure. The data is not naturally relational, so the whole system will be easier to write and much more efficient if you design it this way.

By document centric what I mean is that when a service is requested, the application uses a document template to present a form to the user, then captures the users input as a single document, not multiple columns of data or multiple tables.

There are many choices for document format, two of the most popular are JSON and XML. If you need help with pros and cons etc I think this should be a separate question.

To store your documents you can just use the file system, or a BigTable. You could even store documents as a single column in a relational database but I would urge you away from this option.

Your database schema would look something like this:

/*!40101 SET NAMES utf8mb4 */;

CREATE DATABASE IF NOT EXISTS `services` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci */;
USE `services`;

CREATE TABLE IF NOT EXISTS `organization` (
  `organization_id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`organization_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `request_service` (
  `request_id` int(11) NOT NULL AUTO_INCREMENT,
  `organization_id` int(11) NOT NULL,
  `service_id` int(11) NOT NULL,
  `service_type` int(11) NOT NULL,
  `document` LONGTEXT NOT NULL COLLATE 'utf8mb4_unicode_ci',
  `tracking_no` int(11) DEFAULT NULL,
  PRIMARY KEY (`request_id`),
  KEY `FK_request_service_service` (`service_id`),
  KEY `FK_request_service_organization` (`organization_id`),
  CONSTRAINT `FK_request_service_organization` FOREIGN KEY (`organization_id`) REFERENCES `organization` (`organization_id`),
  CONSTRAINT `FK_request_service_service` FOREIGN KEY (`service_id`) REFERENCES `service` (`service_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `requirement` (
  `requirement_id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `requirement_type` int(11) NOT NULL,
  PRIMARY KEY (`requirement_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `restriction` (
  `restriction_id` int(11) NOT NULL AUTO_INCREMENT,
  `service_id` int(11) NOT NULL,
  `is_allowed` bit(1) NOT NULL,
  `for_active_only` bit(1) NOT NULL,
  PRIMARY KEY (`restriction_id`),
  KEY `FK_service_restrictions_service` (`service_id`),
  CONSTRAINT `FK_service_restrictions_service` FOREIGN KEY (`service_id`) REFERENCES `service` (`service_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `service` (
  `service_id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `requirement_id` int(11) NOT NULL,
  PRIMARY KEY (`service_id`),
  KEY `FK_service_list_of_requirement` (`requirement_id`),
  CONSTRAINT `FK_service_list_of_requirement` FOREIGN KEY (`requirement_id`) REFERENCES `requirement` (`requirement_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;