Is this in the 3NF or not?

Question

I have a table called Transactions

transactions ( TransactionID, CustomerID, DeviceTypeID, TransactionQuantity, TransactionDate, ManufacturerID)

TransactionID → CustomerID

TransactionID → DeviceTypeID

TransactionID → ManufacturerID

TransactionID → TransactionQuantity

TransactionID → TransactionDate

TransactionID → TransactionValue

Where transactionID is a primary key.

CustomerID DeviceTypeID ManufacturerID are all foreign keys.

DeviceTypeID and ManufacturerID are needed to tell what "product" a customer is buying.

However, the is another table "inventory" which basically has

DeviceTypeID

ManufacturerID

These two keys are a composite primary key, and a number or quantity.

inventory ( DeviceTypeID, ManufacturerID, InventoryQuantity )

(DeviceTypeID, ManufacturerID) → InventoryQuantity

Basically, in the inventory, there isnt a device for every manfucaturer. Say,there arent LG boilers in the inventory. But samsung ones are there.

It seems that Transaction Quantity is dependend on DeviceTypeID, ManufacturerID. And manufactuerID is dependend on DeviceTypeID and vice versa but I could be wrong.

It looks like this is in 3NF but i am not sure. I havent seen cases anywhere discussing what happens when you include composite/compound keys in a transaction table and a primary key.

Contextually speaking none of the keys have any realtion with each other in the table.

Answer 1

If there is only one item of a device type from a manufacturer in inventory, then this would be 3NF. This would be the case for the information provided.

However, it is likely that you might have more than one type of LG boiler in inventory table. As you dig into the design more details may arise which break the 3NF conditions.

Typically inventory items mighte have a natural key of manufacturer and manufacturer's part number. This would be complicated if inventory items which can be sourced from different manufacturers are sold or used interchangably. In this case, you would have an inventory (item) id for the inventory table. This would link to a table with a tri-part key (inventory-id, manufacturer-id, manufactuers-part-number).