Insert into table with polymorphic association in database

Question

I want to design a database where some tables have multiple foreign keys.
I am looking for the best design approach.

I found out that this kind of problem is called "polymorphic association" in database. I found the Stack Overflow question Foreign Key to multiple tables and Nathan Skerl's answer very helpful.

But according to the link above, if I want to insert a new user (not a new ticket, but a new user), what would be the insert statement based on the design?

First I insert into the party? How can I retrieve the id to insert into user or group table? Because it has identifying relationship with user and group.

---

Describe relationship for insert query:

    create table dbo.PartyType
(   
    PartyTypeId tinyint primary key,
    PartyTypeName varchar(10)
)

insert into dbo.PartyType
    values(1, 'User'), (2, 'Group');


create table dbo.Party
(
    PartyId int identity(1,1) primary key,
    PartyTypeId tinyint references dbo.PartyType(PartyTypeId),
    unique (PartyId, PartyTypeId)
)

CREATE TABLE dbo.[Group]
(
    ID int primary key,
    Name varchar(50) NOT NULL,
    PartyTypeId as cast(2 as tinyint) persisted,
    foreign key (ID, PartyTypeId) references Party(PartyId, PartyTypeID)
)  

CREATE TABLE dbo.[User]
(
    ID int primary key,
    Name varchar(50) NOT NULL,
    PartyTypeId as cast(1 as tinyint) persisted,
    foreign key (ID, PartyTypeId) references Party(PartyID, PartyTypeID)
)

It seems user has an identifying relationship with the party table: an existing child (user) depends on the existence of the party. Now I want to insert a new user, for example when some user needs to sign up.

What would be the query to insert a new user?

Answer 1

This is an example of a polymorphic association, where the primary key of a number of tables is the foreign key of a super-table.

You would first insert into the Party table, then take that inserted ID and insert that also into the User table:

insert into `Party` (PartyTypeId)
values (1);

insert into `User` (ID, Name)
values (LAST_INSERT_ID(), 'SomeName');