How can I model IS-A relationship on a ledger, when having different classes of products?

Question

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.

Use Case

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 motor data 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)

Current thoughts

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 products table.

Typical Motors from various vendors that are likely to ordered are located in the table motor.

The problem with my approach is more or less apparent in the ledger_item table. I have motor_id and 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.

If 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 category_id and 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?

Answer 1

IS-A Relationship

I don't understand what "is a" relationship we're talking about. A motor is a product?

The database is not the place to define these relationships, your domain class model is. The database tables may map very nicely to this relationship but the database is the data store, not the relationship definition.

Thinking in this way is a gateway to putting business rules in the database. And you're doing that already ...

generic part number of MOTOR on the sales ledger

Obviously "MOTOR" is antithetical to the PK/FK relationships tables need. The "linking" table, ledger-item, holds product and motor keys but then you go around that by slapping "MOTOR" in there and then wildly speculate on table re-design just so you can put "MOTOR" on a ledger instead of the actual motor id. Do that and you'll be fighting the DB design everywhere in your application; and you'll have extra queries every time you get this particular data (product - ledger - motor)

The domain class model is the place to make that substitution. It's universal and easy. And we don't have "MOTOR" for every row on tables, which is the poster child definition of unnormalized, inefficient data storage.

Decoupling odd-ball requirements

If design considerations drive you to putting "MOTOR" in the database then make a separate table. Then I would make that a one-time fetch to put it in a domain object and reference that throughout the application.

If this "MOTOR" madness causes friction in the code then perhaps it can be it's own class. But it's only a single value! Well, this gives you a place to throw any exceptional handling and one-off rules for that thing. And it's easier, cleaner to inject or replace. It can also be part of a larger "business rule" infrastructure - it's just one of a collection of rules applied in your model.

Answer 2

To be fair, I think you are overthinking it.

You will not gain anything by using the proposed model on your database - you'll end up with more or less the same amount of data in the database anyway and you'll ramp up the costs of your queries.

Really, if the motor is a part, deal with it as a part and forget about how many times they will be used. You'll simplify your database design and your client software, will make both easier to expand in the future and will also be able to train new recruits to the Dev Team faster.

So, here's my tackle on this:

• Use the table Products (use plural names for tables, single names for classes) to store all the "things that are sold", regular products or motors.
• Use the generic "MOTOR" part name for motors.

Your proposed solution creates just as many "one-off" objects as putting them inside the Products table. I don't understand why you say it's not desirable, but looking to what you presented on your question it looks like the best way to tackle this issue.

Don't worry about having barely used data on your database - "one-offs". Think about log tables - they are by design full of "one-offs" items that will be there if you need them, but otherwise just sit there doing nothing. They are, as far as I know, not really a problem to most systems that do use them in a sensible way. Products tables will always end up full of things that you won't use ever again - things go out of line, are never sold again, etc, etc. Really, don't worry about it.

If you are worried about having too many "Products" for someone to choose for, use the Category column to filter off Motors from the initial "product combobox" that you probably have somewhere on your system. If the user wants to see the motors, they can very well filter for them.

If you can't use your Category Column for it, slap an extra "Type" column to the product table with some macro types, like "Motors", "Plastic Parts", "Metal Parts", or whatever makes sense in your system.

Answer 3

I upvoted both radarbo's and TSar's answers. I agree with them.

Here's a proposed model:

• A 'MOTOR' category exists in table PRODUCT_CATEGORY
• Specific motors exist in PRODUCT along with other non-motor products
• LEDGER_ITEM name implies there should be a LEDGER entity
• MOTOR holds columns that only aply to motors, such relationship is 1:1, non-mandatory and is a FK on the MOTOR side poiting to PRODUCT