0

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?

12
  • LAST_INSERT_ID() maybe? What code do you have so far? Jun 13, 2021 at 9:39
  • Forget about "polymorphic" and focus on whether "many:many" is 'right' for the relation.
    – Rick James
    Jun 13, 2021 at 17:36
  • @Charlieface : Its on the design, not in the code; I was asking on what is best practice to achieve multiple relation to one table that I visit the link I mention. After that I was wondering ok, if the answer in the link is the right way, so how can I insert new users ? If some body want to sign up what would be the right query and way to do that. Jun 14, 2021 at 5:56
  • @RickJames : So you say the right way is to crate many:many relation to drive what I want, true? And It would be confusing to deal with polymorphic association? Jun 14, 2021 at 6:00
  • I have no idea what you are trying to represent as you haven't shown us. That particular article would normally refer to multiple 1:1 tables, not many:many tables. I can't tell you what database design you need if you have told us nothing about the relations between the different concepts in it. All I know is that you have user, party, group and a wild guess tells me that 1:1 is probably not right, it's probably 1:many. But you need to give us much more info. Jun 14, 2021 at 6:26

1 Answer 1

0

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');
1
  • So we should use las_insert_id function to get the last id. Thank you so much Jun 26, 2021 at 4:02

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.