I'm working on a web application for our business with my brother. Our application is pretty simple, and will be developed with php and mysql.
- Clients and providers ABMs
- Articles Inventory (we do not manage stock, because we resell from our providers)
- Purchases
- Account balance for our clients and providers
I'm stuck on accounting tables… Here is my model:
I'm in doubt about the account table; I'm thinking about use a trigger when purchases change Status (delivered to client) then updating the Account balance but I don't know if this model is good for managing Accounts for my Clients.
I'm looking for opinions about my mistakes!! Thank you.
Best Answer
Entity names should be singular (
client
,purchase
, etc. notclients
,purchases
, etc. )Relationship from
purchase_article
topurchase
should be one to many ( beingpurchase
the one end )purchase_article
toarticle
should be one to many ( beingarticle
the one end )provider_article
toarticle
should be one to many ( beingarticle
the one end )provider_article
toprovider
should be one to many ( being provider the one end )provider_article
Pk should be(idProvider, idArticle)
, if you insist on using a surrogate then be sure to create an unique constraint on(idProvider, idArticle)
Phone
andTelephone
? Seems like violating 1FN to meunitPrice
andPrice
?Purchase
andAccount
is not inverted ?client
,provider
, etc., be sure to create unique constraint on business key to avoid duplicates.