Which would be the best way to do this?
-
Hello all,
I am working on a PLC project with MySQL.
I have some parts that must be grouped and some parts that will never be.
In the tParts table I thought on adding an idGroup and creating a table tGroups where I would have an id, the reference of the group and the number of parts that form the group (from 1 to n parts).
Then in the tParts table I would have that idGroup, the position in the group (from 1 to n too).
My doubt comes here: for the grouped parts this is a no brainer, idGroup must be set to not null and be a foreign key to the tGroups table. That way, I can protect accidental deletion of groups when they have parts assigned.
Should I simply allow this idGroup field to be NULL? would that allow having ungrouped parts without breaking any rule? I mean, non-grouped parts would have a NULL here, grouped parts would have the id of the real group.
Is that the best way to solve this issue?
Thank you all for your help.
-
I use T-SQL so it may not be quite the same, but I'd implement this with a link table.
Table tPart would have field idPart plus other part info; table tGroup would have field idGroup and other group info. Then table tPartGroup has fields idPart and idGroup, each as foreign keys to their respective tables. No NULL entries needed.
Adding or removing parts in groups only updates the tPartGroup table. The foreign keys prevent deleting a non-empty group or deleting a grouped part. If a part can only be in one group, then this just adds a UNIQUE constraint on the idPart field (I assume MySQL has this). Counting parts in a group is a simple query.
Of course, if the parts are ordered in the group, then an additional field is needed on tPartGroup.
As to what's the best way...?
