I have basically two tables in the database, one is named “Valve”, and the other is named “Channel”. This is a basic “1 to many” relationship, which means one Valve can control many Channels. See the image below:

In this figure, you see four sub-figures. The first sub-figure (day0), it shows that Valve0(V0) is controlling 3 Channels(C0, C1 and C2), Valve1(V1) is controlling the Channel3(C3).

This is a very basic “1 to many” relationship, so I create two tables, one is named “ValveTable”, and the other is named “ChannelTable”. In the “ChannelTable”, it has a foreign key named “ValveID” which points to the primary key of the “ValveTable”.

But in my cases, the tree structure will change day by day. In the day1, the property of the C0 will changes, so do I need to make a new copy of C0 row in the “ChannelTable”? Because I need to remember the day0’s tree hierarchy. Also, the C2 is connected to V1 in day1, so the connection is changed.

In the day2, C1 is removed from V0. And in day3, a new Channel C4 is added to the ChannelTable, so it got the new tree hierarchy.

I’m not sure how to design this whole database structure. Do I need to add a third table? just like a Conneciton Table or something like a “junction table” like a “many to many” relationship database?

Any good ideas? The whole picture is very large, and this is a very simplified question, the core issue is that I need to keep the tree structure change history in the database.

Thanks.