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 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
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:
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