locked
Database design (ERD )for Inventory Management System RRS feed

  • Question

  • Dear All,

    I am going to develop a simple Inventory Management System software using C# .NET for my learning. After searching different forums, many people have suggested to first create a database design for the software. I want a database design, in short, an ERD diagram for simple Inventory Management System which shows proper entities(tables), attributes and relationship between entities.

    It would be highly helpful for me as I am newbie to C# and databases.

    Thanks,

    momersaleem

    Thursday, April 18, 2013 11:58 AM

Answers

  • momersaleem,

    In a real application, the problem domain will dictate how complex your schema needs to be. I think if you just remember "this can be more complex than what I'm doing", you'll be fine.

    But that said, thinking about it, it might be good for you to get some experience with dealing with the difference between the way data is represented in the schema and how it's represented to the user. Name might be a good one to deal with. If you split it into the three canonical attributes, you can then play around with re-combining them in different ways for reporting purposes.

    Sorry, I expressed myself badly. I meant to keep the Product_Price, which is already in your schema, and add the Order_Product.Product_Price.

    Your naming conventions are fine.


    Rebecca M. Riordan

    Monday, April 22, 2013 2:53 PM
  • okay, FWIW

    CUSTOMERS:
    Names are potentially the most complex things you'll ever deal with. Most designers use a GivenName, Surname, Salutation pattern, and as a general rule that works pretty well. But sometimes it's overkill, and what you've done in a single field is just fine. If you're only ever going to be generating an invoice, for example, it might work. (That's actually a pretty rare situation.) Sometimes, it can get incredibly complex. I designed a system for a charity in the UK once. Getting the name pattern correct was absolutely critical--they were asking people for money--and because they regularly corresponded with members of the aristocracy, the pattern was crazy--titles, even names, change based on context. I'd say easily 30% of the design effort went into getting that right.

    There aren't any generic "right" answers. You need to balance the questions the system needs to answer against the complexity of entering and manipulating the data.

    Addresses are only slightly less complex. A single field is hardly ever going to work here, because somebody's gonna want to sort by country, state, zip....something.

    The trick with both of these is to figure out what questions the systems need to answer.

    What's the difference between a purchase and an order?  They're usually the same thing, which doesn't mean you're wrong, but what are you picturing here?

    Pricing: Any order system needs to manage two very distinct bits of data that are easy to confuse. The price in the Product entity is the current price. The price in the Order entity is the selling price. Not at all the same thing--current price is almost certainly going to change over time. Selling price won't.

    Why did you include a quantity field in the Products table? Is it meant to represent stock on hand?


    Rebecca M. Riordan

    • Proposed as answer by Naomi NEditor Wednesday, April 24, 2013 6:34 PM
    • Marked as answer by Maggie Luo Tuesday, May 7, 2013 10:16 AM
    Sunday, April 21, 2013 3:28 PM
  • momersaleem.

    Yes, Product:Supplier is usually a many-to-many. Sometimes it's one-to-one, but it depends on the Product. If it's "artichokes", there will almost certainly be multiple suppliers. If it's "Glenfiddich 12 Yr Single Malt", there will only be one.

    I would expect a company to make multiple purchases from a supplier, so that would be a 1-to-many. You'll need ProductID, SupplierId and either a date or (better) PurchaseOrderNumber to uniquely identify the table.


    Rebecca M. Riordan

    • Marked as answer by Maggie Luo Tuesday, May 7, 2013 10:16 AM
    Sunday, April 28, 2013 12:09 PM

All replies

  • I don't know that there's such a thing as a "simple Inventory Management System", but fwiw, there are a bazillion sample schemas at databaseanswers.org

    Rebecca M. Riordan

    Thursday, April 18, 2013 12:24 PM
  • Microsoft has a sample database (and multiple versions) called AdventureWorks.  Why don't you start with that since it is also used for many programming examples.
    Thursday, April 18, 2013 12:56 PM
  • Dear Rebecca,

    Thanks for your quick reply and your provided link seems really helpful.

    My apologies for confusion, I used word 'simple' in terms of easy i.e. I just need an ERD with 3-4 entities and their relationship.

    I have chosen 3 entities (Customers, Orders and Products) from the link below and their relationships as per my requirements.

    http://www.databaseanswers.org/data_models/customers_and_inventory_mgt/index.htm

    Here you can notice that the diagram just uses the general term 'tablename_details' for attributes instead of specifying/classifying attributes in detail. Could you please mention all attributes for these 3 entities with proper Primary Keys and Foreign keys for me. I would be very thankful to you for this.

    Thanks,

    momersaleem

    Thursday, April 18, 2013 12:59 PM
  • momersaleem,

    There was no confusion. I was teasing you. I'm sorry it didn't translate into type.

    Are you building a "real" application for an actual company, or is this something that you're building as a learning exercise? (It's a good one, if that's the case.)


    Rebecca M. Riordan

    Thursday, April 18, 2013 1:21 PM
  • Dear Rebecca,

    My pleasure for being teased. lol

    I am building it as a learning exercise.

    Thanks,

    momersaleem

    Thursday, April 18, 2013 1:29 PM
  • momersaleem,

    In that case, no I won't give you details. As part of your exercise, think about what attributes you might want to keep track of. (It doesn't matter if you get them wrong.) And then come back with your ERD and I'll give you my opinion.

    (I'm a hard taskmaster, aren't I? LOL)


    Rebecca M. Riordan

    Thursday, April 18, 2013 2:40 PM
  • Dear Rebecca,

    First of all, after reading your profile on different sites , I am really pleased and feeling lucky for being taught by you. Thanks for your support. 

    Secondly and definlitely you're really a hard taskmaster that's why it took me 2 days to complete my ERD (hopefully, with many mistakes :)) for a simple Inventory Management System. LOL!

    Please click the following link to view ERD Diagram: 

    ERD Diagram for Inventory Management System

    I have added 3 more tables (Order_Product, Purcahse, Product_Purchase) along with (Customers, Orders and Products). Please have a look on the diagram and mention my mistakes so that I can avoid them in future.

    Thanks,

    momersaleem

    Saturday, April 20, 2013 4:47 PM
  • First of all, after reading your profile on different sites , I am really pleased and feeling lucky for being taught by you. Thanks for your support.

    Well isn't that a scary thought!

    momersaleem, I don't know anything about database design that you can't learn, and somebody taught it to me. Lots of somebodys. I'm just trying to pay it forward.

    But I'm afraid that having said that, I'm not going to be able to look at your design until tomorrow. But you will hear from me then, I promise. (FWIW ;) )


    Rebecca M. Riordan

    Saturday, April 20, 2013 5:52 PM
  • momersaleem, the link is dead...can you re-post it?

    Rebecca M. Riordan

    Sunday, April 21, 2013 10:37 AM
    • Edited by Kalman Toth Sunday, April 21, 2013 1:55 PM Make link live
    Sunday, April 21, 2013 1:34 PM
  • okay, FWIW

    CUSTOMERS:
    Names are potentially the most complex things you'll ever deal with. Most designers use a GivenName, Surname, Salutation pattern, and as a general rule that works pretty well. But sometimes it's overkill, and what you've done in a single field is just fine. If you're only ever going to be generating an invoice, for example, it might work. (That's actually a pretty rare situation.) Sometimes, it can get incredibly complex. I designed a system for a charity in the UK once. Getting the name pattern correct was absolutely critical--they were asking people for money--and because they regularly corresponded with members of the aristocracy, the pattern was crazy--titles, even names, change based on context. I'd say easily 30% of the design effort went into getting that right.

    There aren't any generic "right" answers. You need to balance the questions the system needs to answer against the complexity of entering and manipulating the data.

    Addresses are only slightly less complex. A single field is hardly ever going to work here, because somebody's gonna want to sort by country, state, zip....something.

    The trick with both of these is to figure out what questions the systems need to answer.

    What's the difference between a purchase and an order?  They're usually the same thing, which doesn't mean you're wrong, but what are you picturing here?

    Pricing: Any order system needs to manage two very distinct bits of data that are easy to confuse. The price in the Product entity is the current price. The price in the Order entity is the selling price. Not at all the same thing--current price is almost certainly going to change over time. Selling price won't.

    Why did you include a quantity field in the Products table? Is it meant to represent stock on hand?


    Rebecca M. Riordan

    • Proposed as answer by Naomi NEditor Wednesday, April 24, 2013 6:34 PM
    • Marked as answer by Maggie Luo Tuesday, May 7, 2013 10:16 AM
    Sunday, April 21, 2013 3:28 PM
  • Oops. Some teacher I am. I forgot to get you your homework ;)

    Seriously, if you want to have another go at the ERD and re-post, I'll look at it for you again. (Or not, as you choose, lol.)


    Rebecca M. Riordan

    Monday, April 22, 2013 9:51 AM
  • Dear Rebecca,

    Thanks for you suggestions.

    As I am going to develop IMS for learning purposes so I think I wouldn't need to go in detail regarding Customer name and addresses. However, I am still thinking of adding country attribute in customers' table which I think will be helpful to sort out customers.

    What's the difference between a purchase and an order?  They're usually the same thing, which doesn't mean you're wrong, but what are you picturing here? Purchase entity will be used to keep record of purchases you made and an order entity will be used to keep record of orders that cutomers placed.

    Pricing: Any order system needs to manage two very distinct bits of data that are easy to confuse. The price in the Product entity is the current price. The price in the Order entity is the selling price. Not at all the same thing--current price is almost certainly going to change over time. Selling price won't. Does it mean that I'll change the price attribute for product to current_price and add selling_price to order table which will help to keep record of price at the time of order?

    Why did you include a quantity field in the Products table? Is it meant to represent stock on hand? Yes you are right. It represents stock in hand.

    Could you please recheck the entities relationships as I am not confirmed whether these are correct or not?

    Thanks,

    momersaleem

    Monday, April 22, 2013 1:00 PM
  • Dear Rebecca,

    Teacher I have just posted my reply except another ERD. lol!

    I am trying to understand it through our discussion at the moment.

    As you know this session will be ended soon so I was thinking of keep in touch with you for future help and discussions. Is there any way to get in touch with you Teacher? :)

    Thanks,

    momersaleem

    Monday, April 22, 2013 1:18 PM
  • momersaleem,

    I think it's good to do this in public because it might (might!) be useful to somebody else, but I have a public email: rebeccamarye at msn dot com.

    The relationships look correct, but I very much doubt that "purchase_from" would be sufficient. You need a supplier entity that's related to both purchases and products (and is another thing that can get complex if there are multiple suppliers)

    Yes, you do need to add price to Order_Product. Naming conventions are a bit contentious, but I think it's more important that you have a convention than what it is. I'd probably use Product.Price and OrderItems.Price, but that's just me. There's nothing wrong with what you've named things.


    Rebecca M. Riordan

    Monday, April 22, 2013 1:27 PM
  • Dear Rebecca,

    Yes, definitely it is helpful in public for others but sometimes it will be very helpful to directly contact you in case of any urgent matter.

    The relationships look correct, but I very much doubt that "purchase_from" would be sufficient. You need a supplier entity that's related to both purchases and products (and is another thing that can get complex if there are multiple suppliers). I think your point is correct but at the moment I want to keep it simple and easy so that I can start developing an application. I will keep notes of your suggestions for future references. What do you suggest?

    Yes, you do need to add price to Order_Product. Here I am little confused that if I add price attribute only under Order_Product entity, it will help me to keep record of price for a specific order to generate receipt but how shall I manage the stock in hand if its not mentioned in products entity?

    I'll try to use proper naming conventions from now.

    Thanks,

    momersaleem

    Monday, April 22, 2013 1:59 PM
  • momersaleem,

    In a real application, the problem domain will dictate how complex your schema needs to be. I think if you just remember "this can be more complex than what I'm doing", you'll be fine.

    But that said, thinking about it, it might be good for you to get some experience with dealing with the difference between the way data is represented in the schema and how it's represented to the user. Name might be a good one to deal with. If you split it into the three canonical attributes, you can then play around with re-combining them in different ways for reporting purposes.

    Sorry, I expressed myself badly. I meant to keep the Product_Price, which is already in your schema, and add the Order_Product.Product_Price.

    Your naming conventions are fine.


    Rebecca M. Riordan

    Monday, April 22, 2013 2:53 PM
  • Dear Rebecca,

    As per your suggestion, I have added "Supplier(SupplierID, Name, Address, Phone, CompanyName)" entity in the ERD. You have mentioned that Supplier entity will relate with both products and purchases entities. I think Supplier and Products entities has many to many relationship so I have added another entity "Product_Supplier(Products_ID, Supplier_ID)". Please confirm if its correct?

    For this system, last thing for me is to define relationship between Purchases and Supplier entities so here I am little confused that what would be relationship between them? Please guide me to make their relationship and defining their attributes.

    Thanks,

    momersaleem

    Wednesday, April 24, 2013 6:13 PM
  • momersaleem.

    Yes, Product:Supplier is usually a many-to-many. Sometimes it's one-to-one, but it depends on the Product. If it's "artichokes", there will almost certainly be multiple suppliers. If it's "Glenfiddich 12 Yr Single Malt", there will only be one.

    I would expect a company to make multiple purchases from a supplier, so that would be a 1-to-many. You'll need ProductID, SupplierId and either a date or (better) PurchaseOrderNumber to uniquely identify the table.


    Rebecca M. Riordan

    • Marked as answer by Maggie Luo Tuesday, May 7, 2013 10:16 AM
    Sunday, April 28, 2013 12:09 PM
  • http://www.slideshare.net/copo7475/inventory-management-system-16598132#
    Wednesday, April 16, 2014 5:20 AM
  • http://www.slideshare.net/copo7475/inventory-management-system-16598132#
    Wednesday, April 16, 2014 5:20 AM
  • You may find here also

    http://www.slideshare.net/copo7475/inventory-management-system-16598132#

    Wednesday, April 16, 2014 5:21 AM