Why do you want exactly 3 Tables? My suggestion would be to set up
Tables that deal with related information, and if you wind up with 3,
that's not necessarily better than 2 or 4. What I think you want is for
each record in a Table to contain fields that all relate to some
real-world concept (such as an assembly) that you need to describe.
Does one record in the [assembly] Table identify an assembly that
contains several (generic) [parts]? And that each part listed in the
[parts] Table is a generic type (= catalog number, for example) to which
several physical parts, with separate [serial no] values, correspond?
If so, I'd include a [parts_ID] value in a record in the [serial no]
Table to indicate what type of part has that serial number.
(Many-to-one relationship, many serial numbers to one part type.)
I might also include an [assembly_ID] field in each [parts] record,
indicating to which assembly the part belongs. However, if a part type
may belong to two or more [assembly] items, then you'd need an
additional Table to support a many-to-many relationship: each record
would contain a [parts_ID] and an [assembly_ID] to indicate that the
specified part belongs to the specified assembly. You might also
indicate, in the same Table, how many of that part are in the assembly.
So at this point we'd have 4 Tables in the system; each of these would
hold a different type of information.
Alternatively, if you have a need to track the individual serial numbers
to an assembly, you could omit the 4th Table and include in the [serial
no] Table an [assembly_ID] field (and remove [assembly_ID] from the
[parts] Table). How you should design the Tables depends on how you
want to organize your information.
If you could post example records from your Tables, along with an
example of the type of results you'd like to see, it would be easier to
give you more specific advice on getting those results.
-- Vincent Johns <
[email protected]>
Please feel free to quote anything I say here.
