How best to relate table into database

Question

I've worked with databases on and off, but this is my first time designing one from scratch. Apologies if this already has an answer somewhere, I couldn't find anything satisfying.

The objective is to store quality-testing data during product assembly. A variable number of tests may be run on each unit, so I have many-to-one related tables for tests and builds.

The next table to add is a list of part numbers in the build (each unit is made of several hundred parts). From a physical and logical standpoint, it makes sense that these should be related to the Builds table. However, the client stated they must be related to the tests because parts are sometimes switched out between tests if a mistake is identified.

It seems like a huge waste of space to duplicate hundreds of parts each time a test is re-run, when only one or two are actually changing. However, I can't think of a better way. Any ideas?

Thanks in advance.

Answer 1

It sounds like you're running the test on the build itself, rather than the parts in particular. So it is as if the build has got versions, with each one being different from the one before because a part was changed.

That suggests to me that you need a build_version table that relates to the set of parts, and which is the subject of the test.

If there are a great many parts but only a few of them change between versions then you might have a build_version_part_changes table that expresses the relation between a build_version and its parts in terms of parts added and parts removed.

So if there is a test failure and parts are then changed, a new build_version record is created with an associated set of parts changes. The new build_version is then subject to another test.