Need help with database schema

[MyName13]

I've designed a database schema for an inventory management system.

Spoiler

 

Some tables are missing but this is the most important one.For the same product, product type, manufacturer and supplier are repeated so there is redundancy (there are also products with the same name from different manufacturers like bread, fruit etc...)which is why I designed this

 

Spoiler

Which one is better and how should I improve them?

[bowrilla]

What kind of db are you using? In a relational db you want to avoid redundancy. Are you using product names as IDs? You really don't want to do that. Especially not if products of the same name can be provided by various manufacturers.

[Limecat86]

1 hour ago, MyName13 said:

I've designed a database schema for an inventory management system.

  Hide contents

 

Some tables are missing but this is the most important one.For the same product, product type, manufacturer and supplier are repeated so there is redundancy (there are also products with the same name from different manufacturers like bread, fruit etc...)which is why I designed this

 

  Hide contents

Which one is better and how should I improve them?

The second is better but there's still a lot to improve. Like ProductSuppliers feels like it should be a many2many intermediate table. You should read about many to many relationships.

Also if you work with money. Always store prices in their smallest unit. For Dollars and Euro's that would be cents. Don't use floats but instead use integers.

 

Warehouse should be an entity if there are more that one warehouses. Then store a foreign warehouseID key in your inventory table

 

[Limecat86]

Created a schema quickly of how I would approach this. I left out some tables from in your schema so you can add them yourself.

 

  

products__suppliers is an intermediate table here. It simply connects suppliers to products and vice versa. Suppliers may supply one or many products and products can be supplied by one or many suppliers. 

[MyName13]

5 hours ago, bowrilla said:

Are you using product names as IDs? You really don't want to do that. Especially not if products of the same name can be provided by various manufacturers.

Which one do you mean?

 

I've made few mistakes.For every manufacturer with the same product I will have new typeID in productType which creates redundancy.It should be like this: 

 

1)manufacturerProduct - [ManufacturerID], Product,  ManufacturerName (FK to manufacturers).Every ManufacturerName, product pair gets its manufacturerID

 

2)ProductType should have nothing to do with manufacturers: [Product], ProductType.

 

3)ProductSuppliers: [supplyID], manufacturerID (FK referencing the same attribute in manufacturerProduct), supplierName (FK to suppliers).

 

Basically what Limecat did.

 

6 hours ago, Limecat86 said:

Like ProductSuppliers feels like it should be a many2many intermediate table.

Your productsSuppliers has 2 many to one relations, just like in my schema.

[programmer]

14 hours ago, Limecat86 said:

Also if you work with money. Always store prices in their smallest unit. For Dollars and Euro's that would be cents. Don't use floats but instead use integers.

Without providing the caveat not to do calculations with money in integers/decimals, that is dangerous advice (in Java, always use Big Decimal and round at the end unless you have a very good reason not to.)

 

Also, if you use integers to store currency make the documentation excessively clear - one of my client's ATMs stated running out of money due to a bug with this (thankfully the code was only deployed to a few test machines. I assume it created a mess on the compliance and logistics side as well, but that was outside of my part of the project.)

8 hours ago, MyName13 said:

I've made few mistakes.For every manufacturer with the same product I will have new typeID in productType which creates redundancy.It should be like this: 

Why are you tracking the inventory by supplier? Are you trying to determine if inventory from one supplier has a higher defect/return rate/sell rate then another or something? Is this academic or operational?

[MyName13]

9 hours ago, programmer said:

Why are you tracking the inventory by supplier? Are you trying to determine if inventory from one supplier has a higher defect/return rate/sell rate then another or something? Is this academic or operational?

So I can know who is supplying which products?

[programmer]

5 hours ago, MyName13 said:

So I can know who is supplying which products?

If two items are fungible then why would you track where they came from (not that there aren't potential reasons, I just don't see them here?) 

 

If you want to track orders, have a table for that - something like: [productId, quantity, vendorId / orderId, date]. If the prices vary over time then track the price paid - if you get promotions sometimes then you might track that as well (so you can try to analyze and optimize future purchases) - but that historical data is separate from current inventory levels.

 

Likewise, you may want a sales table to track sales over time so you can do analysis and optimization of inventory levels. This is separate from current inventory levels.