Multiple parents for AspNetUsers table for Asp.Net Core 3.1 app

Question

I have the following table in MySQL.:

CREATE TABLE `aspnetusers` (
  `Id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `UserName` varchar(256) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `NormalizedUserName` varchar(256) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `Email` varchar(256) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `NormalizedEmail` varchar(256) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `EmailConfirmed` tinyint(1) NOT NULL,
  `PasswordHash` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci,
  `SecurityStamp` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci,
  `ConcurrencyStamp` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci,
  `PhoneNumber` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci,
  `PhoneNumberConfirmed` tinyint(1) NOT NULL,
  `TwoFactorEnabled` tinyint(1) NOT NULL,
  `LockoutEnd` datetime(6) DEFAULT NULL,
  `LockoutEnabled` tinyint(1) NOT NULL,
  `AccessFailedCount` int NOT NULL,
  `InsertUser` varchar(100) DEFAULT NULL,
  `InsertDateTime` datetime DEFAULT NULL,
  `UpdateUser` varchar(100) DEFAULT NULL,
  `UpdateDateTime` datetime DEFAULT NULL,
  `ParentId` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`Id`),
  UNIQUE KEY `UserNameIndex` (`NormalizedUserName`),
  KEY `EmailIndex` (`NormalizedEmail`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

Now my Asp.Net Core 3.1 app uses the above table, but I want to change in that way that a user can have null, one or multiple parents.

What is best (and minimal) way to make this modification?

Should I create a new table that connects the UserID with ParentID?

Answer 1

To have multiple parent ids you can use a Bridge table see the shortened first example.

so you have multiple parent even more than 2.

The second sample a user can only have 2 parents, and the must be define before hand.

The gui is different of course, but the second can only have 2 parents, for more you need to add another columns.

CREATE TABLE `aspnetusers` (
  `Id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `UserName` varchar(256) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `NormalizedUserName` varchar(256) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `Email` varchar(256) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `NormalizedEmail` varchar(256) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  PRIMARY KEY (`Id`),
  UNIQUE KEY `UserNameIndex` (`NormalizedUserName`),
  KEY `EmailIndex` (`NormalizedEmail`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

INSERT INTO aspnetusers VALUES
  ('1','1','1','1','1'),('2','1','2','1','1'),('3','1','3','1','1')

Records: 3  Duplicates: 0  Warnings: 0

CREATE TABLE User_Parent(
  user_id    varchar(255) NOT NULL,
    `ParentId` varchar(255) NOT NULL,
  add_more_comLumns_that_describes_relationship int
  , PRIMARY KEY(user_id,ParentId)
  ,
    FOREIGN KEY (user_id) REFERENCES aspnetusers(Id),
    FOREIGN KEY (ParentId) REFERENCES aspnetusers(Id))

INSERT INTO User_Parent VALUEs ('1','2',1),('1','3',2)

Records: 2  Duplicates: 0  Warnings: 0

CREATE TABLE `aspnetusers2` (
  `Id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `UserName` varchar(256) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `NormalizedUserName` varchar(256) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `Email` varchar(256) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `NormalizedEmail` varchar(256) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `ParentId` varchar(255) DEFAULT NULL,
  `ParentId2` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`Id`),
  UNIQUE KEY `UserNameIndex` (`NormalizedUserName`),
  KEY `EmailIndex` (`NormalizedEmail`),
    FOREIGN KEY (ParentId) REFERENCES aspnetusers2(Id),
    FOREIGN KEY (ParentId2) REFERENCES aspnetusers2(Id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

INSERT INTO aspnetusers2 VALUES
  ('1','1','1','1','1',NULL, NULL),('2','1','2','1','1',NULL,NULL),('3','1','3','1','1','1','2')

Records: 3  Duplicates: 0  Warnings: 0

fiddle