I don't know how to resolve my situation:
create Table VIPUsers(
id int primary key,
parent int references Parent(id)
);
create Table Parent(
id int not null,
childType int not null,/*indicates ChildA or ChildB*/
unique (id,childType)
);
create index on Parent(id);
create Table ChildA(
parent int references Parent(id),
propertyA int
);
create Table ChildB(
parent int references Parent(id),
propertyB date
);
There is M to N relationship between tables VIPUsers
and ChildA
, ChildB
.
Table Parent
is a junction table containing information whether VIPUsers
uses instances of ChildA
and/or instances of ChildB
I need to add rows:
insert into Parent (id,subtype) values (1,1)
insert into Parent (id,subtype) values (1,2)
This gave me an error 'duplicate primary key' so I deleted the primary key and I'd use an index instead, just as it is shown above.
But now I can't create tables VIPUsers
, ChildA
, ChildB
as I get the error
'there is no unique constraint matching given keys for referenced table ...'
I know it is because VIPUsers
would not reference a unique row. But that's what I need. I need a join where for each parent I get one or more childTypes. For example:
VIPUsers ParentId childType
1 1 1
1 1 2
If I make Parent.id
a primary key, there will be only one childType
for each user. If I remove primary key, DBMS starts complaining about referencing a non unique column.
My goal is to then cross join the above example table with ChildA or ChildB on ParentId to get table like this:
VIPUsers ParentId childType ChildA.propertyA ChildB.propertyB
1 1 1 50 null
1 1 2 null 2/2/2017
And based on the childType
I'm able to tell what kind of information this row represents.
I don't like putting childType
into each Child
table because it would allow for nonsense values like ChildA being of type ChildB and vice versa.
What are the usual ways of going about this?
Best Answer
After some clarification (in comments and chat), it seems that:
ChildA
andChildB
are subtypes.VIPUser
has a many-to-many relationship with allChild
entities ("VIPUser "uses" Child").VIPUser
andChildType
(stored inParent
), essentially what types ofChild
a `VIPuser can use ("VIPUser "can use" a child of ChildType").Then the relationships between entities can be shown in the diagram
(I renamed
Parent
toVIPCanUse
):