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.

  • 1
    2 quick points, you are keeping what appear to be monetary amounts as floats not decimal and you might want to consider would you want to attach multiple addresses/phones/emails to a provider or client. – Sign Jul 8 '14 at 15:18
  • You are right, i will change data type on my table. All this information on Client table we use for purchases, many clients use differents ways to make a purchase (most rason is because they are from differents province) someone send us an email, or call us.. In providers i think its not necesary too much fields like clients... – JMHerrer Jul 8 '14 at 15:23
  • 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.
| improve this answer | |
  • Hi @user61852, Thanks for your comments. On response to your points: On my model I modified points 1-4 on relationship because I did it bad with Mysql Workbench. Purchase_article to Article doesnt exist anymore, now Its joined to provider_article. 7. Phone do reference to Cellphone and Telephone to an Local Phone at a physical place. 8. Purchase -> account were delete, now make reference on Client -> Accounts. – JMHerrer Jul 19 '14 at 14:09
  • @JMHerrer If my answer was of any help, please vote it up. – Tulains Córdova Jul 19 '14 at 16:31

I wouldn't comment on minor details that you can fix later. There are several structural issues here:

  1. Why do you have a PurchaseUnitPrice in Purchase? This should be on purchaseArticle
  2. Similarly why do you have a PurchaseQuantity in Purchase? This should also be on purchaseArticle
  3. Some thing tells me UnitPrice is the price you got from the provider and price is the price you sell. If that is true, do NOT put in article the profit, if you want an argument, we can talk further to explain
  4. Purchase status shouldn't be a boolean. You might want to have several options later such as 'unpaid', 'partial-paid', 'returned', etc
  5. I assume you have a table providerArticle because an article might come from several providers. This way, make sure PurchaseArticle has a relationship with ProviderArticle not Article because you want to know the exact provider providing the article that you just sold. If my assumption is not true, simply make a many-to-many relationship between the article and the provider
  6. Account should have a one-to-one relationship with Client' not withPurchase`
  7. If you really want to keep track of the payments of each purchase, put a one-to-many relationship from Purchase to Payment (a new table entity)
| improve this answer | |
  • Hi randomA, thanks for your reply! 3.You are right at this point. UnitPrice refer to provider price, and Price it is when i sell it to my clients. Where should i put Articles Profits?, and why should I move it from Articles Table. 4. Purchase status was modified to accept different options. 5. Ok, i understand. if I add an article ID it's could be from different provider and not from who exact sell me.. 6. Relationshio modified. 7. Payment table what function should do? Just track each order from each client with his payment ammount ? – JMHerrer Jul 19 '14 at 13:35

I think you need to to do a bit more analysis as to how time affects your model.

  • An offer to sell is at the current price
  • An item (or group of items) is sold at a particular price that won't ever change.
  • You seem to be heading down the right track with the total price being in the purchases table, but then purchases_articles links to a table that has price and profit fields that may no longer be relevant.

What I am highlighting is that it is necessary to copy fields into "logging" tables, even though this looks like denormalisation. Classical data modelling theory tends to ignore this inconvenient reality..

| improve this answer | |

You could use the two foreign keys as a combined primary in providers articles. This to make sure you only have one of each provider article. Otherwise you could get duplicates with just a different ID. And try to use human readable IDs (like email addresses). Besides that I don't really see anything besides maybe the decimal-float thing.

| improve this answer | |

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.