Vehicle spare parts inventory datadase model

Question

I am modeling a database to be used for vehicle spare parts inventory management and I am stuck...

Some spare parts can be broken down from a kit into component parts. I would like to create a model that supports this, but do not know how to build it.

Spare parts can be issued in sets or pieces. The database should keep information about how many complete sets we have and how many have been broken into pieces. For instance, one piston ring set will have 6 different individual piston rings.

Can you point me in the right direction?

Answer 1

Great, the part/whole modelling problems.

Here is how LedgerSMB does it. We are likely to change this at some point but this part works well enough.

Basically we have two tables, called parts and assembly. The exact names/layout will change over time but this basic concept will probably stay the same.

In the parts table, we have parts (goods in inventory), services, and assemblies (as you put it, sets). In the assembly table we store the assembly to connect with, the part or assembly that is a part of it, and the number required. This allows sets to be parts of other sets recursively. This effectively creates a components tree in assembly and a sales component tree in parts.

It works well enough, especially when done on a database which supports WITH RECURSIVE common table expressions (i.e. not MySQL...)

Of course then you have a table or two to manage inventory movements and this would handle the sets and their deconstruction according to your business rules.

Answer 2

From what you describe i get this.

table VehiclePart
id
name
totalStock
..other fields
set_parent_id (this is an id from the same table that this spare part belongs to. If is part of a set that is, otherwise null)


So lets say we have these data
id name totalStock ... set_parent_id
1 part1 134 ... null
2 part2 23 ... null
3 part3 5 ... null
4 part1_1 134 ... 1
5 part1_2 134 ... 1
6 part3_1 5 ... 3
7 part3_2 10 ... 3
8 part3_2 3 ... null


As you can see we can have in the table we have described all the cases of data we need.

part1 is a set that we have 134 inside our warehouse.
... it consists of part1_1 (1 per set)and part1_2 (1 per set)

part2 is a simple part not a set that we have 23 inside our warehouse.

part3 is a set that we have 5 inside our warehouse.
... it consists of part3_1 (1 per set)and part3_2 (2 per set)

part3 has been splitted sometime in the past and we used its part3_1 sub parts. What is left is 3 parts of part3_2.

So with the above table you have total of items per set but also per part itself

Using from part1 the sub part part1_2 and losing a set by splitting it to parts for example would create below table after changes applied.

id name totalStock ... set_parent_id
1 part1 133 ... null
2 part2 23 ... null
3 part3 5 ... null
4 part1_1 133 ... 1
5 part1_2 133 ... 1
6 part3_1 5 ... 3
7 part3_2 10 ... 3
8 part3_2 3 ... null
9 part1_1 1 ... null


I believe with common logic you can figure the rest.