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.
2
-
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 ) - Relationship from
purchase_article
toarticle
should be one to many ( beingarticle
the one end ) - Relationship from
provider_article
toarticle
should be one to many ( beingarticle
the one end ) - Relationship from
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)
- What’s the difference between
Phone
andTelephone
? Seems like violating 1FN to me - What’s the difference between
unitPrice
andPrice
? - Are you sure relationship between
Purchase
andAccount
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.
2
I wouldn’t comment on minor details that you can fix later.
There are several structural issues here:
- Why do you have a
PurchaseUnitPrice
inPurchase
? This should be onpurchaseArticle
- Similarly why do you have a
PurchaseQuantity
inPurchase
? This should also be onpurchaseArticle
- Some thing tells me
UnitPrice
is the price you got from the provider andprice
is the price you sell. If that is true, do NOT put inarticle
theprofit
, if you want an argument, we can talk further to explain - Purchase status shouldn’t be a boolean. You might want to have several options later such as ‘unpaid’, ‘partial-paid’, ‘returned’, etc
- I assume you have a table
providerArticle
because anarticle
might come from several providers. This way, make surePurchaseArticle
has a relationship withProviderArticle
notArticle
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 thearticle
and theprovider
Account
should have a one-to-one relationship withClient' not with
Purchase`- If you really want to keep track of the payments of each purchase, put a one-to-many relationship from
Purchase
toPayment
(a new table entity)
1
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..
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.