locked
Need Help in ER Diagram of (Point of Sale) RRS feed

  • Question

  • This is ER Diagram of POS that I've made it has been made simple. I just want you to tell is there anything wrong in my ER Diagram and what else modules can I add while keeping the overall Design simple.

    nraina
    Tuesday, May 24, 2011 12:08 PM

Answers

  • Hi,

    1) Here Sales has BillNo, but there is no direct relationship between Sales entity and Billing entity.

    2) Sales entity have ItemName, Qty, and PerUnitPrice which are also present in Stock entity. I understand for Qty as the values might be different for Stock and Sales entities, but do not understand about ItemName and PerUnitPrice as the value will always be same for both of these entities and as we already have foreign key ItemId in Sales entity then we don't require other two attributes here.

    3) In the Sales entity, there is an attribute named "Mode". I suppose this is billing mode that how the customer / dealer is paying the bill (Cash, Cheque, Draft, Credit Card, Debit Card, Sodexo coupon etc.). If my understanding is correct then this attribute should be part of Billing entity and not the sales entity. Also there should be some master table for this billing mode.

    4) Stocks have multiple offers and Sales, but I am not sure what is the use of these offers as these are not being related to any other entities though I do not much about your business.

    5) there look many other corrections required in this ER diagram.

    Thanks,

     


    Neeraj Tiwary - Please mark the post as answered if it answers your question
    • Proposed as answer by NeerajTiwary Wednesday, May 25, 2011 6:58 AM
    • Marked as answer by Peja TaoEditor Tuesday, May 31, 2011 4:39 AM
    Wednesday, May 25, 2011 5:58 AM
  • Hi,

    1) Need more clarification regarding CustomerAcc and DealerAcc. Is this Bank Account of customers and dealers or anything else?

    2) As per your statement "The Client will purchase stocks from Dealer and gets his Stocks updated and the Customer buys items from Client this will also updates the Stocks", In the current ER model there is no entity of Client here. As per your statement it looks like client is interacting with dealer and customers both.

    3) There is another statement " We've kept option of providing Offers and Returning Sales", I think here offers mean the offer / discount that client if offereing to customers to sell their goods. If that is the case then Offer entity should interact with billing entity also as here in billing entity if a customer based on some attractive offer chooses a product then the billing entity should reflect that.

    4) Billing entity should also have ItemId and Qty as billing is done for some items only.

    Thanks,

     


    Neeraj Tiwary - Please mark the post as answered if it answers your question
    Wednesday, May 25, 2011 7:21 AM

All replies

  • Guys Need Help  . . . Please spare time for this query ?????????????
    nraina
    Wednesday, May 25, 2011 5:03 AM
  • The biggest problem with asking for assistance is that we do not know the business requirements for your solution so it will be difficult for us to offer any improvements or comment on what you've done.
    Jeff Wharton MSysDev (C.Sturt), MDbDsgnMgt (C.Sturt) MCT, MCPD, MCITP, MCDBA Blog: MrWharty.wordpress.com
    Wednesday, May 25, 2011 5:24 AM
  • The biggest problem with asking for assistance is that we do not know the business requirements for your solution so it will be difficult for us to offer any improvements or comment on what you've done.
    Jeff Wharton MSysDev (C.Sturt), MDbDsgnMgt (C.Sturt) MCT, MCPD, MCITP, MCDBA Blog: MrWharty.wordpress.com

    Well, Thanks for your valuable reply, I am creating a project on Point of Sale. I'll be using WinForms and Database will be on SQL Server 2005. It's the first time I've been assigned to any project so forgive me for my ignorance of not proving the details. We want to keep the project as simple as possible both economically and usage so that even a small retailer can also afford our software. Now we've been doing analysis for the last few days and we concluded our ER Diagram. 

    ER Diagram has been made simple. The Client will purchase stocks from Dealer and gets his Stocks updated and the Customer buys items from Client this will also updates the Stocks . We've kept option of providing Offers and Returning Sales. 

    I've made my best attempt to make this ER simple and I just want you all respected veterans to verify my ER for any mistake and if you think I can add up some other modules also while keeping the overall design simple then do tell me.

     


    nraina
    Wednesday, May 25, 2011 5:36 AM
  • Hi,

    1) Here Sales has BillNo, but there is no direct relationship between Sales entity and Billing entity.

    2) Sales entity have ItemName, Qty, and PerUnitPrice which are also present in Stock entity. I understand for Qty as the values might be different for Stock and Sales entities, but do not understand about ItemName and PerUnitPrice as the value will always be same for both of these entities and as we already have foreign key ItemId in Sales entity then we don't require other two attributes here.

    3) In the Sales entity, there is an attribute named "Mode". I suppose this is billing mode that how the customer / dealer is paying the bill (Cash, Cheque, Draft, Credit Card, Debit Card, Sodexo coupon etc.). If my understanding is correct then this attribute should be part of Billing entity and not the sales entity. Also there should be some master table for this billing mode.

    4) Stocks have multiple offers and Sales, but I am not sure what is the use of these offers as these are not being related to any other entities though I do not much about your business.

    5) there look many other corrections required in this ER diagram.

    Thanks,

     


    Neeraj Tiwary - Please mark the post as answered if it answers your question
    • Proposed as answer by NeerajTiwary Wednesday, May 25, 2011 6:58 AM
    • Marked as answer by Peja TaoEditor Tuesday, May 31, 2011 4:39 AM
    Wednesday, May 25, 2011 5:58 AM
  • Thanks for your reply  . . . 

    1. Yeah I made mistake regarding not having a direct relationship between Sales and Billing Entity. I'll correct it .

    2. Actually I used ItemName and PerUnitPrice in Sales for reporting purpose and yes I've foreign key ItemId, so that won't be necessary. I'll correct it.

    3. The "Mode" in Sales is basically telling that the Item being sold is through Regular selling or through Offer. "Mode" Entity possess a default value of Regular unless specified "Offer". There is no option we've kept for Credit Card / Debit Card.

    4. "Offer" Entity is a weak entity, can have multiple offer in a given date range. The usage is like during the Bill Payment it'll be checked if the current date falls in any date range of Offer Entity. Then we'll be providing Offer . We just need to set the Offer and it'll be checked through each Bill Transaction. I want to know how can I make Offers expire ??

    5. Well for other mistakes do enlighten me. i want to know I'll be doing my best for correction. If I misunderstood anything then do tell me ....

     

    New One



    nraina
    Wednesday, May 25, 2011 6:11 AM
  • Hi,

    1) Need more clarification regarding CustomerAcc and DealerAcc. Is this Bank Account of customers and dealers or anything else?

    2) As per your statement "The Client will purchase stocks from Dealer and gets his Stocks updated and the Customer buys items from Client this will also updates the Stocks", In the current ER model there is no entity of Client here. As per your statement it looks like client is interacting with dealer and customers both.

    3) There is another statement " We've kept option of providing Offers and Returning Sales", I think here offers mean the offer / discount that client if offereing to customers to sell their goods. If that is the case then Offer entity should interact with billing entity also as here in billing entity if a customer based on some attractive offer chooses a product then the billing entity should reflect that.

    4) Billing entity should also have ItemId and Qty as billing is done for some items only.

    Thanks,

     


    Neeraj Tiwary - Please mark the post as answered if it answers your question
    Wednesday, May 25, 2011 7:21 AM
  • 1. CustomerAcc and DealerAcc are like to keep track of transactions if incase Customer pay half bill later, so we've made that Entity for Dealer also if any Amount is left.

    2. Client I am referring to user operating software, we can think it as logically Stock is updated by Dealer Entity while Purchasing items from Dealer and Stock is updated by Bill (Customer' s Bill) Entity while purchasing items from Stocks. Client is not an entity it is the operator.

    3. Offers can be anything like "if total amount of Bill is more than so so Rupees you'll either get discount or any quantity of items provided in the Offers Entity". We can check from front end if the Bill of current date falls between the specified date in Offers Entity. if this method is leading wrong then plz do specify else option. Actually I didn't understood how can Offer will interact with bill it is master table for Offers.

    4. BillDetail is the Entity for keeping track of Customer's ItemId and Qty as BillNo is PK so a Customer can have multiple Items in a given Bill. If leading wrong plz specify.


    nraina
    Wednesday, May 25, 2011 7:32 AM