This is a question on how to model database entities and relationships when it comes to storing persistent data and dealing with differently-handled types of products.
Company sells various many
Products that are made inside the company, and various many
Motors, which are not made at the company and are treated differently.
Each product has a specific individual part number, but Motors are not the main business of the company and as such are taking a back seat in a sense that no matter what type of motor is sold, it is always listed under generic part number of
MOTOR on the sales ledger. This is also because more often than not, the motor is a "one-off" part, only passing through, and often has to be custom-purchased, and is not kept in permanent stock.
At the same time, when possible, there is a need to know exactly which motor is being sold for inventory purposes, and there are several ways to deal with that - conceptually.
- keep separate
motordata table with motors from typical vendors
- on the ledger next to MOTOR part number, describe exact motor in description
- move Motors into main products table but then have to create a lot of throw-away motor part numbers because of one-offs (not desirable and not the route I want to go)
I have an ER diagram where I have defined a few tables, like so:
All products the company makes with their own specific part number are located in
product table. Generic part number of "MOTOR" is also in the
Typical Motors from various vendors that are likely to ordered are located in the table
The problem with my approach is more or less apparent in the
ledger_item table. I have
product_id, that discern product and motor respectively. If it is a
product, I then pull the
product data and use that product's own specific part number, which can be discerned from
product_id == MOTOR, that means to look for the motor in the motor table to find out the specific motor I need to read
motor_id and go into
motor table to find out the specifics.
For the cases where a motor that is not in the motor table is added (a one-off part), I can only rely on the
product_id keys to tell me it is a motor, not rely on
motor table and then read the description in the ledger.
Can this be ... made better? Or do I have it right that I use
category_id as a way to signify
IS-A type relationship in the ledger and then dig into the appropriate foreign keys for specifics?