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 ABMsArticles Inventory (we do not manage stock, because we resell from our providers)PurchasesAccount balance for our clients and providersI'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.
MySQL Database design - Opinions
design;database;mysql
Entity names should be singular (client, purchase, etc. not clients, purchases, etc. )Relationship from purchase_article to purchase should be one to many ( being purchase the one end )Relationship from purchase_article to article should be one to many ( being article the one end )Relationship from provider_article to article should be one to many ( being article the one end )Relationship from provider_article to provider 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)What's the difference between Phone and Telephone ? Seems like violating 1FN to meWhat's the difference between unitPrice and Price ?Are you sure relationship between Purchase and Account is not inverted ?If you insist on having a surrogate PK on client, provider, etc., be sure to create unique constraint on business key to avoid duplicates.