0

Consider this table from a multi-database, modularized-monolith application:

CREATE TABLE `Forms` (
  `Id` bigint(20) NOT NULL AUTO_INCREMENT,
  `Uuid` uuid NOT NULL DEFAULT uuid(),
  `Key` varchar(400) NOT NULL DEFAULT uuid(),
  `Slug` varchar(400) NOT NULL DEFAULT uuid(),
  `IsActive` bit(1) DEFAULT b'1',
  `Title` varchar(400) DEFAULT NULL,
  PRIMARY KEY (`Id`),
  UNIQUE KEY `IX_Forms_Unique_Guid` (`Guid`),
  UNIQUE KEY `IX_Forms_Unique_Key` (`Key`) USING HASH
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci

The Id is the primary key, only for foreign keys inside the same database (module). For example, in the Fields table we have FormId of type bigint. Uuid is unique. It is used as the key for cross-database reference (logical foreign keys). For example, when we want to add some data about this form in the SEO module (SEO database) we use this UUID. The Key is for front-end/back-end communication and code readability. For example, in the front-end code, we might see /form/get?key=contactUs. And Slug is for the end-user and SEO purposes only. /forms/contact-us.

Based on the 3rd normalization form:

The key, the entire key, nothing but the key

This table lacks normalization. UUID and Key are both primary key candidates, because they are both unique.

I can change it to:

CREATE TABLE `Forms` (
  `Key` varchar(400) NOT NULL DEFAULT uuid(),
  `Slug` varchar(400) NOT NULL DEFAULT uuid(),
  `IsActive` bit(1) DEFAULT b'1',
  `Title` varchar(400) DEFAULT NULL,
  PRIMARY KEY (`Key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci

This is simpler. The Key can be used for:

  1. Intra-database physical foreign keys
  2. Inter-database logical foreign keys
  3. Front-end/backend communication

Based on internal benchmarks we performed, we saw very little difference (almost 10 milliseconds over 10 million records) between the integer primary key and string primary key in joins and views.

So, is it OK if we decide to drop Id and Guid and only use Key as the primary key to normalize our databases? Is that a common practice?

4
  • Depends on a bunch of things: will the key ever change? How wide is the key in practice (400? can it be less?) If a join is only used for existence checks then the join can be eliminated entirely. Commented Jun 19 at 13:10
  • @Charlieface, that post explains the pros and cons. I asked about the commonality. I want to know that other people have done it and it went well. That's the reason I asked. Otherwise I already know a lot of pros and cons about it. I just want to make sure that I'm not the first person doing so. Commented Jun 19 at 13:16
  • Best practices and "what did you do" aren't really meant for Stack Exchange websites. Probably best posted on Reddit etc. Commented Jun 19 at 13:19

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.