I have an Inventory DB. the DB mainly manages the equipments, different kinds of them, some has a serial number and some don't (like small connectors).
I have a table that contain all these equipments. When the equipments with no serial number are added, they usually contain a quantity number like 200 RJ45 ethernet connectors. When they are ordered out of the warehouse, probably 56 RJ45 connectors are ordered. the orderdetails table contain only the equipment ID of the equipment. So, I will not be able to select the same equipement item again, but I have used only quarter the equipment quantity.
What can I do to redesign these tables (equipment, orders, orderdetails)
I'd just maintain a running total of the "commodity" products. If you want to use identical code for all types of products, then you can keep a running total of the serial number products as well, and this total will always be either one or zero.
If it's not practically useful, then it's practically useless.