Hi,
According to my colleague which takes care of our databases there is an issue in the ERD (v3.0.0) concerning the relation between setID in sets table with specimenSet, compartmentSet, mmSet, unitSet, aggregationSet, qualityIndSet, missingessSet in parts table. A foreign key must point at a primary key which contains always unique values. SetID can contain several identical values and thus cannot be a primary key neither a foreign key. A table linking sets and parts could solve this. This tables would contain a variable with all possible setID and could be used as a primary key toward the foreign key setID and toward foreign keys specimenSet, compartmentSet, mmSet, unitSet, aggregationSet, qualityIndSet, missingessSet. Or maybe set them as FK with partID in parts tables.
Hello - thank you for this!
The issue is an ERD issue for sure, rather than a structural one in practice (or I believe it is).
The problem arrises, I think, because setID is a foreign key, but in the ERD in the sets table it doesn’t show where that foreign key is coming from. Within the sets table, setCompID is the PK/CK for the table, while setID and partID are FKs, both of which come from the partID field in the parts table. The issue is that setID then gets referenced back in the parts table under the various specimenSet, compartmentSet, mmaSet, etc. This creates cycling in SQL (self-referential cycle). The logic here though was that because these are reference tables, they aren’t really something that users are going to have to populate or edit locally, and so these kinds of cycling dependencies shouldn’t create a real problem. So we opted to not show the partID → setID linkage in the ERD to avoid a problem. Alternatively, we could show the partID → setID linkage and not show the sets.setID → compartmentSet etc. linkages, and define them instead as “joins”. Which I think could work?
I would welcome any thoughts of feedback! More tables are always bit of a headache, so I like to avoid having to add them where possible, but if if it’s not possible then you’re right that a table to better manage these linkages might be the best bet.
Let me know!
For some visual representation:
This is what we currently have:
This would be the option with an implied “join” for the specimenSet, compartmentSet, mmaSet etc. fields but with the partID → setID key relationship made explicit:
And this is the “factual” but self-referential cyclical (and thus problematic) representation of the current structure:
hello,
Thanks for the explanations.
I think having setID as foreign key with partID as primary key is the best solution. Regarding the last picture, the blue foreign keys could be linked to the primary key partID. It will be a primary key/foreign key relation within the same table and it ensures that specimenSet, compartmentSet, mmaSet, unitSet, etc. exist as partID.
That’s a good idea! I think the idea was to maintain the continuity to show that sets are all defined in the sets table. But I suppose that can still be clear even without the cycling identities. I’ll make the change to look like this ahead of the 3.0.1 release.
Thank you again!
