2

I want to draw the ER model of a store which The scenario is like this: They have list of Goods and Some Seller which sells them to customers they want to know how much they get profit from their selling and also how Much money they have From Selling The Goods And also they have Some Expenditures Every day That are from these money which they earn from their selling.And Mostly Every week they send these money to the bank, Here is the model which I have Drawn enter image description here But The problem is here that I don't know Where should I Store The Total money of the Sales So If I insert a row in Expenditures table The Total money Should be decreased. IF you Guys Have any better Model For This Scenario Please SHARE WITH ME

2

Not specific to your question but in general do not put sales into an expenditure table. The name of the table suggests that the table contains information about money leaving the business, not coming in - it will confuse the reader and hinder maintainability in the future. I suggest you create a second table called sales and put your receipts in there.

There are very good example database designs that you can find online (I googled bookstore database schema and quite a few hits came up that would be useful to you).

Additionally, if you were going to do this seriously, then I'd look into the general ledger system or otherwise known as double entry book-keeping system which will help you model this a bit better. I'm not an accountant, but wikipedia seems to have a decent link on it:

https://en.wikipedia.org/wiki/Double-entry_bookkeeping_system

1
  • 1
    +1 for double entry bookkeeping. This is the only proper way to track financial transactions in a professional system. – Joel Brown Feb 16 '16 at 21:51
0

In a normalized database for what you want, the total amount of money shouldn't be stored in a column anywhere. You should get that value through querying. I would recommend the following changes:

  • Add a date or datetime field to Expenditures, to track when the Expenditure occurred

  • Add an amount field to Expenditures, to track how much was spent

  • Consider moving the "Price" field in the Sales table to a "Price" field in the Goods table. This is because price seems to be more of a property of the Good being sold rather than the sale itself.

    Then, for example, if you wanted to get the total profits for February 16 2012, you can do a query like:

SELECT
    (
        SELECT
            SUM( s.unit * g.price )
        FROM
            Goods g JOIN Sales s
                ON (s.idGood = g.idGoods)
        WHERE
            date = '2016-02-16'
    ) -
    (
        SELECT
            SUM(amount)
        FROM
            Expenditure
        WHERE
            date = '2016-02-16'
    ) AS profit

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.