I'll admit up front that I'm not a database guru. That's why I need you guys. I've been frustrating myself trying to build some tables that handle this issue:

1) Many locations
2) Many departments per location
3) Many types of products to track.

Normally I'd think of a location table with an id, a department table with an id and a product table with an id. Then I'm thinking an inventory table that matches the first three (location 1, department 3, product 10, quantity 7). My problem is in the details of the products. I'm guessing the common traits are kept in the product table but the more specific things in the inventory table. For example, there are only 2 or 3 types of products we're tracking. It's either food, equipment or consumables. But if each type of product has 5 specific traits then, in my mind, you end up with 10 columns for each item (3 products x 5 traits minus the 5 that apply to that product) that are blank because those traits don't apply to that item.

But I could also track things by department tables since each department will keep track of mainly only 1 type of product. Food services has food inventory, Laundry has chemicals and the supply room has consumables like pens, paper and forms. However that ends up with the same problem as before where you have to have the columns for all types of products if you're going to let a department track more than one type of product.

please help me clear this up!! Thanks in advance.

So you have three different business entities each with its own product stream, and you would like to track them using a single set of tables.
You may also want a complex constraint that dictates what products are carried by what departments, either specifically or by type.
Can you give some examples of the product attributes?

It's a prison setting. Each unit has a kitchen, a laundry and a small unit supply warehouse. Every unit has the same items which come from master warehouses (not part of the equation right now). So each department (kitchen, laundry, etc) has items specific to them. Food in the kitchen, detergent in the laundry, etc. But they also have items that are common to everyone. For example cleaning suppplies. Where I get tripped up is the product specific facts. For food items they want to know things like case quantity, unit of measure (for recipes), servings per jar or can. But they also have kitchen equipment which have facts like serial numbers, date of purchase. Since everyone has the same pool of items to choose from I can break the items down so that their tables have only their columns. maybe a master inventory table with common traits for every product (id, description, price) then product type specific tables so that you don't end up with a table that has 30 columns and each item may only use 10 of them because the other columns don't apply.

Maybe I'm just making it more difficult that it really is but I'm sure the topic of inventory management has been around for some time. Reporting needs to be in place so that we can find out, for example, how many cans of peaches are at a specific unit or how many are there total across all units. Or a report to tell us when a unit is down to 1 tub of detergent, etc.

...and what might be handy is to create separate Views for each product type, including only the columns pertaining to that product type.
This will simplify your application development, and if you ever need to split the data off into separate tables you will be able to do so without breaking your app, simply by modifying the views.

Frankly, I'd go ahead and put all the columns in the Products table, NULLS be damned.
Store them as varchar and they won't take up more space than they need.
If you start getting upwards of 50+ columns in that table, then consider busting them off into product attribute tables with 1-1 relationships to the Products table.

So if I understand you correctly, I lump all the products together and let there be a row for each item by the unit and by the department. Example, floor cleaner. 3 departments x 95 units = 285 rows describing on-hand quantities of cleaner. Facts about floor cleaner such as name and price are in a different table since they don't change (except maybe once a year for the pricing). I'll look into the views usage. Anything to make this simpler would be great.

What's a unit?

Sorry. A unit refers to the prison location. Dallas Unit, Houston Unit, etc.

The item description/properties are stored in an item table.

The department properties are stored in a department table

the locations (units) are stored in a locations table

The inventory is a table containing a quantity, an item reference, a location reference, and possibly, a department reference.