Page 1 of 3

help in DSS

Posted: Tue Aug 26, 2008 8:13 pm
by zplits
greetings everyone. good day.

i'm working on a project that enables the manager to enter their expenses and revenue.
In the expense page there are 6 input fields, namely date, expense, unit, cost, grand total and description. And there are 7 buttons namely, new expense,save, delete, clear, print, edit expense and go to revenue.

My problem's are:
1. In MySQL table. What datatype should i use in the expense and grand total field? It is about money. And what datatype should i use in the date field? lastly, what should be my primary key for this?

2. How many tables must i have?

3. I have found a working system but it was written in vb. And that, I'm wondering if am i be able to add grid fields in my page in order for the manager to manage his/her saved expenses directly on that grid? I know there is no grid field in html. But do you have an alternative idea for it?

by the way, the revenue page will be the same as the expense page.
I hope someone replies. I'll be waiting for your aid.

Thank you in advance

Re: help in DSS

Posted: Tue Aug 26, 2008 10:10 pm
by Stryks
For storing currency values, I tend to use a float (length 7 decimals 2). Storing dates can be easily done with datetime (if using formal dates) or timestamp (or int(11) even) if you're storing dates as a timestamp. I'd probably stay with datetime for this kind of job, if for no other reason than to keep the data scannable.

As for how many tables, it's hard to say without knowing all the details. I'm assuming that 'expense' is text describing the expense, 'unit' is number of units, 'cost' is cost per unit, and 'grand total' is 'unit' * 'cost'?

If that were the case, I'd probably just use a single table. You could even use the same table for expense and revenue simply by storing expenses as negative values. Of course, this wont work if you're allowing negative revenues for whatever reason. Then you could have separate tables or include a column to identify one from the other. Or you could go for two tables. But assuming that the same data will be stored in either case, I'd look for a single table solution.

Now, assuming this will be a multi-user system, you're also going to want to add a column for owner id's. For reporting, you'll probably want to then add departments or groups to your user tables to allow overviews of revenue/expenses for groups of people. Maybe this is beyond the scope of your project though. It's hard to know.

The grids issue is a complex one, and I'm not really sure how to answer it, other than by saying, it depends on how much you're willing to learn and grind away at a solution. You can go from a relatively easy system of clicking a row to access a form allowing you to edit an expense / revenue, to dynamically generating a series of textboxes with php (not always a good solution), through to a javascripted table using ajax to do on-the-fly updates to produce an 'editable grid'.

You can really take it as far as you like. Personally, I like to take the route that will work even if javascript is not there. AJAX is great for making dynamic forms and the like, but the form should work if javascript is not available. The 'editable grid' solution is likley to have rendering issues, and will just flat out not work on non-javascript systems.

Anyhow, food for thought.

Cheers

Re: help in DSS

Posted: Tue Aug 26, 2008 10:23 pm
by zplits
thanks for the response sir. I knew it. :) you'll reply. Just a sec sir. Be right back. I'll be presenting the website to the managers. I'll be going back after it. Please stay in touch. Thank you very much. GOD bless you always

Re: help in DSS

Posted: Wed Aug 27, 2008 3:46 am
by zplits
hello sir. There have been a small revision of the system....

here it is:
In the manager's main page, there will be two buttons that will appear, the first button is Quantity Inventory, and the second is the Sales Inventory.

Fields in the Quantity Inventory are:
Date
Type
Item No.
Description
Quantity on Hand
Grid(hopefully) = where the manager can view and edit what he/she had saved in the database

Buttons in the Quantity Inventory are:
New
Save
Update
Delete
Find
Goto Sales Inventory

Fields in the Sales Inventory are:
Date
Type
Item No.
Description
Quantity
Price
Amount
Remarks
Total Sales
Grid(hopefully) = where the manager can view and edit what he/she had saved in the database

Buttons in the Sales Inventory are:
New
Save
Update
Delete
Find
Goto Quantity Inventory.

Please help. Just a simple solution will do.

Re: help in DSS

Posted: Wed Aug 27, 2008 4:00 am
by onion2k
Stryks wrote:Storing dates can be easily done with datetime (if using formal dates) or timestamp (or int(11) even) if you're storing dates as a timestamp. I'd probably stay with datetime for this kind of job, if for no other reason than to keep the data scannable.
Never, ever use a timestamp for storing a date.

Re: help in DSS

Posted: Wed Aug 27, 2008 4:02 am
by zplits
thanks onion2k. mind if i ask why?

Re: help in DSS

Posted: Wed Aug 27, 2008 4:08 am
by onion2k
zplits wrote:thanks onion2k. mind if i ask why?
1. Timestamps take up more space than dates
2. Timestamps are limited to 1970 - 2038. Dates aren't.
3. By default a timestamp column will have ON UPDATE CURRENT_TIMESTAMP switched on, so if you update the row without taking care to maintain the same data the field will be updated to today's date. That will probably break all your data.

Re: help in DSS

Posted: Wed Aug 27, 2008 4:13 am
by zplits
Okay sir, thanks for that warning. I think I will follow what you said. I'll use date instead of timestamp. Would you help me further? But my next question is regarding database. I know you'll answer this.

Is it better if i create 1 table for quantity inventory and 1 table for sales inventory?

Re: help in DSS

Posted: Wed Aug 27, 2008 4:54 am
by onion2k
zplits wrote:Is it better if i create 1 table for quantity inventory and 1 table for sales inventory?
It's not 'better' to use more tables, or to use fewer tables. The number that you need will depend on the specification of the application and what you need to do with the data.

Re: help in DSS

Posted: Wed Aug 27, 2008 5:02 am
by zplits
Okay. How about in this application that i am currently working on? Should i create 1 table for each of the inventories? Would that be the best approach to it?

Re: help in DSS

Posted: Wed Aug 27, 2008 5:52 am
by onion2k
zplits wrote:Okay. How about in this application that i am currently working on? Should i create 1 table for each of the inventories? Would that be the best approach to it?
Explain to me what the difference between the "quantity inventory" and the "sales inventory" is. They're not terms we use in the UK.

Re: help in DSS

Posted: Wed Aug 27, 2008 6:04 am
by Stryks
onion2k wrote: Never, ever use a timestamp for storing a date.
Yeah, you're right of course. Have never really given it that much thought as there has always been a handy date type to use. I do use timestamp from time to time, but mostly for short term timing (db sessions, login locks, etc).

zplits, I think you're going to have to give a very brief overview of each of the sections, what they need to do, and how it will all be used together.

Re: help in DSS

Posted: Wed Aug 27, 2008 6:45 am
by zplits
okay. There will be two websites that deals with the restaurant. Restaurant managers will login to there site then use the Quantity Inventory and Sales Inventory to send their monthly reports regarding the work of the company and submit it to their CEO. then the CEO will be able to see their submitted reports on his/her CEO site. Based on the submission of reports of his/her managers, the system will try to help the CEO decide, hence the name Decision Support system.

Based on the reports the system will forecast what will be next month's top menu. what will be the companies income after how many months of operation, for example a year. What supplies do they need to address.

The Quantity inventory is what they use to encode how many ingredients in their menu.
the sales inventory is what they use to encode their menu. and how much they have earn in it.

Re: help in DSS

Posted: Wed Aug 27, 2008 8:51 am
by Stryks
Your description raises many questions for me. Some of them are just informational really, but others probably a little more important to getting this right.
zplits wrote:There will be two websites that deals with the restaurant
Are you making two separate front ends, or just showing different interfaces based on who the user is?
zplits wrote:Restaurant managers will login to there site then use the Quantity Inventory and Sales Inventory to send their monthly reports regarding the work of the company and submit it to their CEO.
So, are we talking about one database entry for the month, or are we talking one per day? If one row per month, do you need to capture the from and to date for the information, or will results be assumed to be for an entire calendar month?
zplits wrote:Based on the submission of reports of his/her managers, the system will try to help the CEO decide, hence the name Decision Support system
Help them decide what? This is important. What needs to come out defines what must go in.
zplits wrote:Based on the reports the system will forecast what will be next month's top menu.
Top menu? So ... it's going to predict how many of each menu item will be ordered, thus predicting expenses and income based on sale cost - production cost? If so, aren't you going to need to model a menu item expense (ingredients list, ingredient cost, other cost) to income (price charged)? Or maybe that is what you are saying you want to do? I'm not clear. I guess I'm confused about your wording there. If it is to predict a menu, then I would assume that the application would need to be aware of each menu item as an entity, attached to a menu entity. Then if you were going to break it down, ingredient wise, you'll probably want to have ingredient entities attached to each menu item entity. Each ingredient entity would have a 'cost' per 'unit', with each recipe entity having x 'units' of each ingredient entities required, along with a production cost.

I guess you can see where I'm going with this. You really can make it as complex or as simple as you like, and from your description, it's hard to tell how in depth you want to go. With the model outlined above fore example, it could predict pretty much anything, from projected sales through to the required ingredient 'units' to meet that expected demand, but in order to do that it's going to need a lot of detailed data. The most important being a daily sales breakdown of how many of each menu entity was sold. But that starts to add a lot of work.

On the other hand, a simple monthly summary report is quite easy, but I'm not sure how much support it can give any specific decision. I mean, you would expect that no real 'trend' data would be usable in the short term from this simple data. I would think 12 months at least, and even then would just be broad strokes.

Anyhow, after all that, I'm not sure what kind of model you are aiming at. Is it more the complex, or the simple? What it comes down to really is ...

What decisions are to be supported?
What information will be required to support these decisions?

I don't really get a feel of that from your proposed layout descriptions.

Re: help in DSS

Posted: Wed Aug 27, 2008 10:30 am
by zplits
Are you making two separate front ends, or just showing different interfaces based on who the user is?
there will be two websites. Because the owner of the company owns a hotel and two restaurants. So there will be 3 managers, 1 for the lfisher hotel, 1 for the kai restaurant and 1 for the chicken restaurant.
So, are we talking about one database entry for the month, or are we talking one per day? If one row per month, do you need to capture the from and to date for the information, or will results be assumed to be for an entire calendar month?
Which one do you think will be a better idea sir? If it's about one database entry for each month then, the manager won't be able to create their reports daily? Regarding the capture the from and to date, what do you think would be a better idea? because the CEO will be able to see their submitted reports, and will try to compare the previous months into the upcoming months.
Help them decide what? This is important. What needs to come out defines what must go in.
Help the CEO decide what food will be served next month, what are the ups and downs of his company, what he needs to do in order to improve his businesses.
Top menu? So ... it's going to predict how many of each menu item will be ordered, thus predicting expenses and income based on sale cost - production cost? If so, aren't you going to need to model a menu item expense (ingredients list, ingredient cost, other cost) to income (price charged)?
There would be top menu, i mean about top menu is, what does people like to eat in that particular month, what do they want to eat in the upcoming months, data on the menu to be served next month will be based on the reports submitted in previous months.
Is it more the complex, or the simple?
If you can help me make it work with the simple methods, then why do we need complex ones.
What decisions are to be supported?
What information will be required to support these decisions?
What do you mean about what decisions are to be supported sir?

The information that will support the decisions are the sales reports which was being created and submitted by the managers.