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
group table? Because it has identifying relationship with
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) )
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?
LAST_INSERT_ID()maybe? What code do you have so far?
groupand 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.