Newbie: Setting up an Inventory-type Database with parts and assemblies

[WickerChair]

I am building a custom database similar to an inventory database where I have listings for parts and assemblies which are composed of one or more parts.

My end goal is to 1) retrieve a list of all parts for a specific assembly, 2) generate a pull list of all parts required for an order of several assemblies, and 3) generate a list of all assemblies that contain a particular part,

Since I have an inventory of 8K different parts and somewhere on the order of 150K different assemblies, I need help setting up the tables correctly before I go any further.

Inventory-analog.odb (3.5 KB)

[RobertG]

Fill your example with a little bit data. So it will give a hint for people like me what you are trying to do here. Only empty tables won’t help.

One hint directly: Why do you start with HSQLDB. I see the data you will save there. I would switch to Firebird. Set experimental features on and create an internal Firebird database for your project.

[WickerChair]

Inventory-analog-v01.odb (4.9 KB)
Here is the database with a few entries to explain the sort of things that I am working with.

[Villeroy]

In the attached database, the table “PA”, linking parts to assemblies, has an optional ordinal number.
The form reflects the relation between the assembly table and the mapping table. The available parts are selectable from listbox in the PID column. When the lisbox is focussed, you can select a part by typing the abbreviation.
A second main form allows the entrance of new parts that are not yet selectable from the listboxes. After adding one or more new parts, you have to refresh the listbox in the table grid. Refreshing the entire subform, refreshes the contained listbox and the sort order by ordinal number as well. Entries with no ordinal number appear on top.
Assemblies.odb (14.6 KB)