A question of normalization

[phppup]

In a hypothetical situation, suppose I want to record sales data for items in two broad categories: cars and trucks.

There are obviously different makes and models within each category, but all have the same accessories (for the sake of discussion).

However, trucks have a few accessories not available to cars, and vice versa.

The inclusion of these accessories will reflect in the final sale price, which will be determined identically for both categories, and recorded along with basic info (date of order, date of pickup, payment method, etc.).

What would be the correct/best database setup?

One table that incurs some NULL entries of unavailable accessories?

Two tables; one for trucks and one for cars?

Something more?

Reasoning? Pitfalls?

Edited August 24 by phppup
Typos

[gizmola]

You have a base table for a product.  

From there, you have a few different ways of handling categories.  Some people want this to be hierarchical, but in general you will have a category or product_type table, that will be a foreign key in the product table.  

Additionally, people also will have a tag table that has a many to many relationship to product, so that you can apply a variety of tags to describe a product, beyond the single category.

However, it sounds like beyond that your question is how to make a package, because a car or a truck or even a bundle of other products or accessories requires some sort of relational solution.  These types of structures will sometimes appear in inventory systems as a "bill of materials" feature.

So again, return to the original product table.  The question to answer is "How can I have products that are comprised of one or more other products, which might be comprised of a set of products"?

How I have handled this in the past is to create a "product_relationship" table which has a variety of simple descriptions like "is comprised of", "part of package" etc.

For any individual thing that is a feature you want to track, or describe or has an associated price, you will have a product row for that thing.

 

Then you implement a many-to-many relationship between product and itself.  You have to understand how you can relationally manifest a many to many relationship between 2 entities.  

 

The answer is, you need a table with two keys, both of which relate back to the product table.

The product_relationship table will be used to describe how the parent product relates to the child product.

product_product
-----------------
id (pk)
parent_product_id (fk to product)
child_product_id (fk to product)

 

So you have a car that is  product id 1.

You have a package of accessories that might have a name like "sports package" that is product id 2.

The individual accessories that are part of the sports package each have a product row (3-6)

Product 2 will have these product_product rows:

product_product
---------------
id: 1, parent_product_id: 2, child_product_id: 3
id: 2, parent_product_id: 2, child_product_id: 4
id: 3, parent_product_id: 2, child_product_id: 5
id: 5, parent_product_id: 2, child_product_id: 6

 

So now you have the original Car (product id: 1) and the "sports package" (product_id: 2).

So you can now have a product which is "Car + Sports package".

If this is product id: 3, then you have 2 product_product rows (parent_product_id: 3, child_product_id: 1 and parent_product_id: 3, child_product_id: 2).

 

With this type of structure you can package/combine products with other products or packages of products without limit.  

I did not illustrate the product_relationship value in the product_product table, but it is very useful for allowing some database driven business rules to be implemented, based on categories, tags, product_type and the product_relationship. 

[phppup]

Great info, @gizmola.

But perhaps I should rephrase.

Staying with the same premise, I'm really ONLY interested in tracking the history of inventory (at this juncture).

We only sell one type of truck and one type of car (vehicle history is the only focus for now).

I'm going to track when we got the car and the wholesale price with shipping and fees, and, of course, sales data.

All the same info is applicable to trucks EXCEPT that trucks are sold on consignment. So, I need to include a "sell by date" and, if not sold, a "date of return".

Do I include these 2 columns in a single table, and only fill them with data when applicable?

Or do I use one table for cars, and a separate table for trucks ( where the two tables are identical except for two columns?

[gizmola]

On 8/24/2024 at 10:27 PM, phppup said:

Great info, @gizmola.

Or do I use one table for cars, and a separate table for trucks ( where the two tables are identical except for two columns?

Yes I already answered this.  You make a product table. You have a product_type table as a foreign key, that to begin with will have

id  name
1   Car
2   Truck
3   Package
4   Feature

 

On 8/24/2024 at 10:27 PM, phppup said:

Great info, @gizmola.

 

Staying with the same premise, I'm really ONLY interested in tracking the history of inventory (at this juncture).

 

I'm going to track when we got the car and the wholesale price with shipping and fees, and, of course, sales data.

All the same info is applicable to trucks EXCEPT that trucks are sold on consignment. So, I need to include a "sell by date" and, if not sold, a "date of return".

 

Probably the best way to handle this, based on the information you provided is to create a "product_event_type" table and a "product_event" table.

product_event_type
------------------
id   event
1    Receive
2    Sell by
3    Return

Should be clear this allows you to classify an event for a product.  You can of course add other event types to this table if they would be useful.

product_event would look like this

product_event
-------------
id
product_id (fk to product)
event_type_id (fk to event_type)
event_date 
notes

 

The handling of financials is a non-trivial requirement.  It really depends on what you intend to try and do with this data within the system.  

If I was designing this database, there would be requirement gathering and documentation in some manner, and review of the ERD to make sure the structure would support the requirements.  

You should see at this point that the "product" table is the hub of the system, so the recording of financial data would probably be in a ledger type table, that relates to product, and to product_event rows.