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?
LAST_INSERT_ID()
maybe? What code do you have so far?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.