Jump to content
  • Announcements

    • alpenwasser

      Please Use CODE Tags   31 Jan 2016

      Welcome to the Programming and Software Design Section,

      When asking for help with programming issues, please use the code tags to enclose your code, it makes things much more easily readable for the people trying to help you, thus improving your chances of actually getting help.
        To add code tags, click the <> button on the editor toolbar, then enter your code in the code editor that appears. If you are on a mobile device, or prefer to use BBCode, you can use [code] // Your code here // It will be syntax highlighted, though not necessarily corectly. [/code] (but the code editor is more consistent and less buggy).
MyName13

Need help with database schema

Recommended Posts

Posted · Original PosterOP

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

Spoiler

F4hdc.png

 

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

Yk2TB.png

Which one is better and how should I improve them?

Link to post
Share on other sites

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.

Link to post
Share on other sites
1 hour ago, MyName13 said:

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

  Hide contents

F4hdc.png

 

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

Yk2TB.png

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

 


CPU: i7-5820k // MOBO: Asus X99-A // RAM: 32GB Corsair Dominator Platinum 2400MHz // GPU: MSI GTX 1080 FE

PSU: Corsair RM750i // CASE: Thermaltake Core X71 // BOOT: Samsung Evo 850 250GB // STORAGE: Samsung Evo 850 500GB

COOLING: Full custom loop // DISPLAY: LG 34UC89G-B

Link to post
Share on other sites

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.

 

simple_schema.png.d2561141ba7d6155ccab57b15da36dbb.png  

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. 


CPU: i7-5820k // MOBO: Asus X99-A // RAM: 32GB Corsair Dominator Platinum 2400MHz // GPU: MSI GTX 1080 FE

PSU: Corsair RM750i // CASE: Thermaltake Core X71 // BOOT: Samsung Evo 850 250GB // STORAGE: Samsung Evo 850 500GB

COOLING: Full custom loop // DISPLAY: LG 34UC89G-B

Link to post
Share on other sites
Posted · Original PosterOP
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.

Link to post
Share on other sites
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?


Web Developer and Java contractor

Link to post
Share on other sites
Posted · Original PosterOP
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?

Link to post
Share on other sites
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.


Web Developer and Java contractor

Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now


  • Recently Browsing   0 members

    No registered users viewing this page.


×