Database Design - calculating Profits/earnings etc.

[Nexus13]

Hello everyone,

A novice databaser here with some questions. First some background:

My Database is for a service-based industry. I repair video game consoles.
Relevant Tables:
CustomerT
ConsoleT
WorkOrderT

I'm not using Access' 'relationship' feature but rather am handlign my relationships manually through VisualBasic. For example, my ConsoleT has:
ConsoleID, CustomerID (to link), ConsoleType, SerialNumber, DVDserial, Model, Notes, IsActive



One Customer can have many Consoles. Each Console can have multiple WorkOrders for every time I do work on that particular Console.



I've designed my WorkOrderT to include all the same fields as both CustomerT and ConsoleT. That way all data is preserved in case the Customer say sells his/her console to somebody else. I don't want my database to Cascade change all the WorkOrder data associated with that previous customer. make sense? If not... example:

Peter buys xbox 360 and brings to me to get DVD fixed. I now have a Workorder entry that reflects work done.

Peter sells xbox to Geoff.

Geoff bring same 360 to get Red Ring of Death reballed. I will enter Geoff as a new customer, and make new workorder associated with Geoff's name. The previous DVD drive repair WorkOrder will still stay associated with Peter.

*Whew....Sorry for the long background... Its kind of relevant to my question.

QUESTION:
Where should I put information like "Profits", "Income" i.e. Money related stuff. The way I currently have it is that I input the value paid on each invidivual Workorder. It calculates the cost and profit on each Workorder.
Accomplished by putting things like: =([subtotal])*([TaxRate]) in the control Source. Will this kind of thing bog down my database once it starts to get large?

Should ALL calculations stay in queries? I.e. Only do calculations when using a query. Is having a query that calculates things like profits a good way of storing that sort of data? Should I be storing profits/money data into a ProfitsTable separately?

Can Anyone shed some light on the best way to have income information kept/stored.

I really want this database to be somewhat futureproof.

Thanks in advance to all those experienced people that are making time to help underlings like me. One day i'll be like you.... one day.....

[June7]

A principal of relational database is to save raw data, calculate in reports. This is probably why Access2010 offers a new field type - Calculated. Income, Profits (or Losses) would be calculated from sales receipts and expenses.

You might consider an off-the-shelf accounting program like Quickbooks. The cost is reasonable in comparison to the time involved in building your own from scratch. Or you might find one of the published Access templates adaptable to your circumstances.

[rpeare]

Are you attempting to monitor *all* profits and income for your store? or just the profits/income that are related to the repairs? For instance if this is a home business are you recording any equipment you buy specifically to do repairs? If you rent an office space are you including that anywhere in your application? Since you didn't mention them I am assuming that you are *only* concerned with the profit/income of the repair side but you haven't mentioned hourly rates unless those are included in your invoice. You also don't mention keeping track of part costs which impact your profit which is another level of complexity but depending on how in depth you want this database to be it's fairly easily handled.

I would probably set things up a little differently than you have

The CustomerT table is fine as is
The ConsoleT table would not carry the customerID, just the console information
My work order Table would have it's own primary key, and the foreign key of the CustomerT table.
I would have one more table, a work order detail table which would have the WorkOrderID foreign key and then a list of all the things that were done (for instance if you have someone who brings in more than one thing to be fixed or you want to show labor split out from parts etc. And if you do end up tracking part costs apply a markup percentage to the part cost when creating an invoice.

June and I differ on the use of calculated fields in tables, I don't like them at all. I would, as you suggest, build queries that tell me what my income was for a specific period and calculate profit based on original part costs vs sale part costs and charged time vs expected time thus if you completed a repair more quickly than anticipated you could calculate the net loss/gain in dollars based on an hourly rate.

[June7]

Rpeare, we don't actually differ on the use of the Calculated field type. I just pointed out that MS added it new to 2010 and their justification for this was probably the 'save raw data' principal but to allow a new versatility. I haven't used this feature and don't have a strong opinion about it. However, I am generally not at all impressed with the 2010 'enhancements' and don't want to upgrade at work. I just learned yesterday that one of them is macro coding attached to a table. I already don't care for macros so doubt I would ever use this.

Your advice looks sound and should give OP a good start.

[Nexus13]

Hi guys,

thanks for your replies. I did indeed look at quickbooks. Although it is quite a comprehensive program, i found it to be lacking in a few things which led me down the path of making my own custom Access Database. Plus its always good, albeit time consuming, to learn new programs.

@ rpeare,

I did actually do have a few other tables i didn't mention.

ProductsT

a list of all my products I sell and parts I use in my work

MaterialsT

A list of products used in each specific workorder (kind of like your WorkOrderDetailT

ExpensesT

a list of all my costs like rent, tools etc

StockOrderT and StockOrderDetailT

a list of my stock orders, what i ordered, costs of ordered parts, all associated costs like shiping, duties etc with each stock order

I separate my income into two parts for each Workorder. One Part for income made from part sales (draws numbers from stockOrderT pricing). One part for labour income.

So I think I will do as you say and use Queries to calculate based on specific perdiods.

However I'm still unsure about one thing. What purpose would linking the WorkOrderT to the customerT accomplish? Would I still be able to see all the linked video game systems to one customer's account? Cause I do like having that feature in my current database.

thanks!!!

[rpeare]

Look at it this way.

Right now, when someone brings in a console you can bring up the console information and who owns it. If it's not the person in your database you change the information in your database and your history is lost.

If you set it up the way I suggest, when you bring up a console by it's serial number you can see the entire history of who owned that console. I always err on the side of retaining information (hazards of my work) and what you're doing is replacing information that may be useful to you at a later date.

Plus setting things up the way I have suggested work is not related to a console, it's related to a customer. In your setup if a console changes hands you have orphaned any repair information for that console/customer pair. The way I suggest you keep that information permanently and all fields in all tables remain relevant.

Edit: My apologies for misunderstanding your post June!