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.