Need guidance creating parent - child relationship in database

Question

There are a number of questions related to parent/child and many-to-many relationships, but I couldn't quite find one that seemed to make sense for my needs.

I have a database of "samples", let's just consider it some kind of goop in a bucket. Each sample has an associated ID. What I'd like to capture is that any given sample can have a parent (i.e. one sample can be split into multiple new samples by pouring the bucket into a bunch of smaller jars). Additionally, one sample could have multiple parent samples; create a new jar by combining multiple parent samples. The key here is that the child and parent samples represent the same thing (a sample) and should thus be members of the same table.

I started with a samples table, where the columns are id, date, project and parent. The id is an alphanumeric ID (i.e. A0001, A0002) and the parent is an array of ID's that should refer back to the list of samples. In other words, the parent must be a member of samples.

I wasn't able to set a foreign key relationship between id and the list of ids in parent.

Is there a way to do this?

I thought about creating a parents table, but then I might have something like:

samples
-------
id      date          project      parent
A0001   2020/11/01    alpha        []
A0002   2020/11/01    beta         []
A0003   2020/11/05    gamma        [A0001, A0002]
A0004   2020/11/05    gamma        [A0002]

parents
-------
id      children
A0001   [A0003]
A0002   [A0003, A0004]

I still have a situation where I need to use a foreign key relationship between a CHAR and a CHAR[].

I would like to be able to query the information (such as date and project) via the ids available in the array of parents. I think I could handle this outside the database, but it seems like a hack, and not the correct way to go.

For context, I'm using Postgresql v13.0 and Hasura GraphQL v1.3.3.

Any help or advice is appreciated.

"*where I need to use a foreign key relationship between a CHAR and a CHAR[]*" - no you don't. Get rid of the denormalized arrays and create a proper many-to-many relationship. Then you'll have no problem at all to define a foreign key. Additionally: [don't use char](https://wiki.postgresql.org/wiki/Don't_Do_This#Don.27t_use_char.28n.29) — , Jan 16 '21 at 21:06
Also note I'm quite new to database structure/SQL/etc, so forgive my meat-headedness. I think I understand... I originally thought that the parents table shouldn't have multiple instances of the same parent, but after reading a bit more, this seems like it is still kosher with rules of normalization. So something like this in the `parents` table is OK: `(id: A0001, child: A0003), (id: A0002, child: A0003), (id: A0002, child: A0004)` — tdpu, Jan 16 '21 at 21:12
PS Thanks for the hint on `char` usage... it's all `text` from here! — tdpu, Jan 16 '21 at 21:20
Yes, that would also be a similar relationship between orders and products: one orderID can have many productID's to know what an order contains. — Paul T., Jan 16 '21 at 21:41
@PaulT. The main difference is that the parent is also a sample, creating this recursion that I'm having issues wrapping my head around. To enable this solution, I now have a table of `parent` - `child` IDs but I'm not able to have a primary key, as there would be duplicates of both parent and child. — tdpu, Jan 16 '21 at 22:00
..... unless I make my primary key the `parent` + `child` pair... This seems to be what I want — tdpu, Jan 16 '21 at 22:05

score 1 · Accepted Answer · edited Jan 17 '21 at 09:23

Often when talking about relationships, a child has only one parent -- even though this is not quite the way that human relationships work. The canonical example is a tree structure.

You seem to just want a directed graph. That suggests two tables:

samples

sample_id	date	project
A0001	2020/11/01	alpha
A0002	2020/11/01	beta
A0003	2020/11/05	gamma
A0004	2020/11/05	gamma

And

relationships:

parent_sample_id	child_sample_id
A0001	A0003
A0002	A0003
A0002	A0004

edit * I've fixed the missing 0 in the child_sample_id table above.

Need guidance creating parent - child relationship in database

1 Answers1