MySQL – Best Practices for Database Design

databasedesignMySQL

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:

database design
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. 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 me
  • What'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.
Related Topic