help in DSS

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

User avatar
Stryks
Forum Regular
Posts: 746
Joined: Wed Jan 14, 2004 5:06 pm

Re: help in DSS

Post by Stryks »

zplits wrote: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.
I would think daily, or end of week would be best, but ...
zplits wrote: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.
... this implies that in order for the data to be able to tell the CEO what items are selling, you are going to have to enter data this is already broken down. I mean ... let's say your menu has item A, B and C. If you submit a report saying "24 A sold, 400 B sold, 350 C sold", well that will tell you about your products. If you submit a report saying "774 items sold" ... well .... that tells you a lot less doesn't it?

But to be able to report on a specific menu item, the database model has to understand each menu item as an entity, and that entity will need to be kept up to date in order to be useful.
zplits wrote: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.
This is a consideration I cant really answer. It depends on the requirements of the system you are designing really. When the CEO logs in, should they see an up to date overview, or data that is a month old. If the data is submitted monthly, what happens if the data is submitted late?
zplits wrote: If you can help me make it work with the simple methods, then why do we need complex ones.


Well .. the method that you'll use will be the method that achieves your goals. A medium complexity will give you a menu item breakdown for your CEO to view, regardless of how often it is submitted, but if you want to include financial and ingredient information, then that ups your complexity. Also, youhave to decide on these scale issues now, before you get too far along, otherwise the project will scale itself up.

I mean, let's say that we track menu item sales, reported daily. Well, seeing as we already know how many items are sold, if we enter a production cost and a sale cost to each item, we can easily see how much we spent and how much we earned without any additional information being entered by the manager. Ok, but now, if we can do that, then we could also attach a list of ingredients needed to produce each item, including the quantity needed per item. Then we could see how much of each ingredient we needed last month, again without requiring any extra information from the manager report.

But each step complicates your database model, because it needs to be able to express all these elements of each item.

So you need to KNOW ... right now ... what data you are trying to carry. If you just want a bulk financial report (income / expense) per month, then your database will be very simple. If you want to track menu items with costs and ingredient lists, then you start moving up into much more complex database setups.

Bottom line ... no-one can really tell you what the 'best' approach is unless you know 'what' it is you need to store.

Have a think about it, maybe discuss the requirements and so on at your end, and when you can clearly define what it is you need to store, then we might be able to give more specific advice about what database design will fit best.

Cheers.
User avatar
zplits
Forum Contributor
Posts: 158
Joined: Sun Aug 03, 2008 8:59 pm

Re: help in DSS

Post by zplits »

Okay sir Stryks. Tomorrow. I'll be thinking it over. Because the thing that I am concern now is that the price always change. In philippines prices here often change. So that's my problem, if the manager submits the report with the price of each barbeque at 45 pesos each, what happens if next week it will change? Those stored information in the database won't be affected by the price.

Do you have any better idea on how to handle this prob?

I'm very thankful you're here. Thank you very much. I'm a bit ashamed to you now, because I keep on asking for your help, and you also help unconditionally. I hope someday i can repay you for your kindness to me.

Thank you very much.
User avatar
zplits
Forum Contributor
Posts: 158
Joined: Sun Aug 03, 2008 8:59 pm

Re: help in DSS

Post by zplits »

Hello everyone, there have been a lot of revisions to the features of the site. There will be no DSS for the Hotel, DSS is for the two restaurants only.

This will be the sample layout of the manager inventory page: http://img526.imageshack.us/img526/1192 ... reahm3.jpg

Sir stryks you said
What decisions are to be supported?
What information will be required to support these decisions?
The decisions that are going to be supported are as follows:
1. Price projection - includes expense and sales. The CEO will view the reports submitted by his managers. Then there will be a special page wherein he will be able to change whatever he desires. It's just temporary though, this special page will be the page where the system helps the CEO decide, based on the reports that he has seen. For example, after he had seen the reports, He'll go to the Special page and he'll say, what if i lessen the no. of chickens, and increase the no. of hotdogs? After he choose that, a graph will appear and will show the CEO his upcoming income and expense... Do you get it sir?

2. Top menu or rankings - this one talks about the menu, or should i say the highest source of income for their restaurant. The Manager will enter in the sales page the following:
Date:
Item no.:
Description:
Quantity:
Price:
Amount:

The system will calculate what menu entered above is the highest going to the lowest. Then it will rank it and display it in the CEO page. Is it clear to you now?

Thank you very much sir. I hope you won't annoyed with me. I'm sorry, I honestly need your help. Thanks a lot.
User avatar
Stryks
Forum Regular
Posts: 746
Joined: Wed Jan 14, 2004 5:06 pm

Re: help in DSS

Post by Stryks »

I can't say that I entirely 'get it', although the image does really help to clarify. The main problem with the clarification is that it raises even more questions.

The tables shown in the image seem to hold irrelevant data, making it's purpose difficult to see. I mean, the 'Main Inventory' screen would seem to be an 'inventory manager', but then the supplier information is out of place. I mean, you might have bought the beginning inventory from a different supplier (or even the same supplier) for a different price, so the pricing and vendor details aren't really valid. Same with the date really. Is that the date of the view, the date of last update, or the date of the last purchase?

Also, how is the value of usage/expense column defined? You seem to be using the product for the same amount you paid for it? And how can purchases and usage both be an expense? Are you looking to make a provision for loss / spoilage?

The inlaid 'Unit Cost' table looks more the place for the supplier and date items, but it has problems. The 'purchase' line for example seems to just be a heading for the row beneath, and you have two usage rows with no identification of when they were used. If usage date isn't important for this view, then there should be a single usage value.

As for the 'unit of measure' ... well ... I'm not sure what you're trying to achieve with that. Are you trying to define a standard 'unit'? So, say you had an inventory of 1, that would mean you had 1 kilo (40 pieces)? How will that work for liquids (soy sauce)?

Then you go from that, back to making 'menu' decisions. That's where I get most lost. When I try to visualize the relationship between these inventory items to 'menu' items, I wind up with a single menu item being made up of multiple inventory items. As an example, in inventory you have an item 'hotdogs'. So for my menu item called 'hotdog', I'll need to take a hotdog and a bun from inventory to make a final product. But it doesn't seem that this is what you are trying to achieve. Or maybe it is, although I can't see many people coming to your restaurant for a single, unaccompanied onion.

I'm kind of rushed, but I'll sit down when I get a chance and think about this some more. It just seems that the descriptions you giving aren't directly related to the data you show.
User avatar
zplits
Forum Contributor
Posts: 158
Joined: Sun Aug 03, 2008 8:59 pm

Re: help in DSS

Post by zplits »

Hello sir, thanks for the response. I have waited so much for it. Can i clarify it to you? I wish we could talk well, like for example chat, we change ideas in real-time, but if you don't want, I'll stick with this post thingy.

Okay sir, if you have your own way of doing this, can you share it with me? I'll follow you, coz your far more knowledgeable than me. I mean what information should i be displaying in the main inventory. I think you have a better idea. If you don't mind, can you tell me what would be the best thing to do?
User avatar
Stryks
Forum Regular
Posts: 746
Joined: Wed Jan 14, 2004 5:06 pm

Re: help in DSS

Post by Stryks »

Well ... instead of bashing out post after post trying to clarify things ... why not pop on across to devnet chat.

There's now a link in my sig.
User avatar
zplits
Forum Contributor
Posts: 158
Joined: Sun Aug 03, 2008 8:59 pm

Re: help in DSS

Post by zplits »

Sir Stryks. Here are the Decisions that the CEO will be able to have in his site.
1. price projection
2. no. of ingredients to be added or deducted
3. top menu
4. expense and revenue projection
5. every changes made by the CEO will be reflected to the percentage in the graphs

In our database. here are the tables with its corresponding fields
1. restaurant
- id
- restoName

2. menuItem
- code
- menuName
- cost

3. ingredients
- code
- ingreName
- quantity
- price

4. sales
- date
- code
- menuName
- quantitySold
- price
- totalAmount

5. purchases
- date
- code
- quantity
- supplyName
- suppliersName
- amount

6 inventory
- date
- code
- supplyName
- beginBalQty
- beginBalAmt
- purchasedSuppliesQty
- purchasedSuppliesAmt
- suppliersName
- usageQty
- usageAmt
- endingBalQty
- endingBalAmt

If you're available we can chat. I'm online Thanks a lot sir.
User avatar
Stryks
Forum Regular
Posts: 746
Joined: Wed Jan 14, 2004 5:06 pm

Re: help in DSS

Post by Stryks »

Looks like a good starting point.

At this point, you have mapped tables for the basic entities in your application. After you get this cleaned up, you'll need to add a couple of tables to intersect the entities, but we'll get to that.

As the 'code' entry you have only applies to the ingredient entity, you'll only need it in there. The rest are redundant and can be removed.

With the 'sales' purchases' and 'inventory' tables, you can safely drop pretty much anything that is calculated. As a general guideline I try not to store calculated values, if for no other reason than because it's usually redundant. For example, if you have the items cost and quantity sold, you don't need to store the total. You can easily work it out from the info you have.

Given the similarity in entity type between sales and purchases, I think you might be able to merge these two tables into one called transactions. That way you can use positive and negative transactions to describe alterations in your inventory table. This reduces the complexity of your inventory table to just the inventory item, transaction id and quantity. You wont need a quantity in ingredients either.

You could possibly also merge the inventory if you're going to go this route, but it might be neater to keep it separate.

I have a naming convention that I usually use, but you can name them differently if you like. Either way, you'll want to give each table a primary key (auto-increment int(11)) so that when it comes to link the entities, we have a unique id for each row. My revised db list would be as follows.
1. tbl_restaurant
- R_PK
- restoName

2. tbl_menu_item
- MI_PK
- R_ID
- itemName
- cost

3. tbl_ingredient
- I_PK
- code
- ingredientName
- measure_unit

4. tbl_transaction
- TRANS_PK
- date
- description
- vendor
- cost

5. tbl_transaction_items
- I_ID (ingredient id)
- quantity

6 tbl_inventory
- I_ID
- TRANS_ID
- quantity
That should be able to contain all the information that we have discussed here and in the chat. Of course there will probably be revisions and additions, but that is par for the course. This part is mainly about mapping out your data entities.

As for the main intersect table ...
tbl_menu_item_ingredient
- MI_ID
- I_ID
- units
Anyhow, it's late ... so I'll leave you with this for now and I can clarify a bit tomorrow if needs be.

Cheers.
User avatar
zplits
Forum Contributor
Posts: 158
Joined: Sun Aug 03, 2008 8:59 pm

Re: help in DSS

Post by zplits »

Sir, good day. thanks for the infro. Man, you really worked hard for out, it was late yesterday when you're response, and i know you're tired. Thanks for being such a nice person to me.

Sir regarding your revised database setup would it cater all the needs of this image? http://img171.imageshack.us/img171/732/ ... reavb9.jpg

and one last thing, what should be a better name for the main inventory, unit cost and unit of measure? because I'm planning to change it into something more descriptive into its corresponding page.

Thanks once again. So thankful for all your support.
User avatar
Stryks
Forum Regular
Posts: 746
Joined: Wed Jan 14, 2004 5:06 pm

Re: help in DSS

Post by Stryks »

Well ... let's put it this way. You'll have access to all the same information, but it wont be in the format you use in that image. This is mainly because, well ... the grouping of the information in the image is not coherent.

As an example, your 'unit cost' table shows start, usage and purchase information over multiple rows but uses unique columns for each piece of information. Also you show no date range for it to be starting from, despite giving starting values.

And as for units of measure ... as I mentioned in a post above, I'm not entirely sure what you were trying to capture with that table.

Having said that, the information will be available, so if you wanted to collect it and present it as you have, then I guess you could. But for me, the way you express it is at the very least confusing. You take pieces of information from various unrelated sources and present them together in table form. I sat looking at that image for something like 5 minutes the first time I saw it trying to figure out exactly what you were trying to express from the information you show.

In the end it only made sense by mentally removing columns of information from the tables. Like in 'Main Inventory', it makes much more sense if you block out the beg. balance, suppliers name and ending balance columns.

Anyhow, think about that, and try to come up with a way to explain what the unit of measure tabloe was doing, and we'll go from there.

Cheers
User avatar
zplits
Forum Contributor
Posts: 158
Joined: Sun Aug 03, 2008 8:59 pm

Re: help in DSS

Post by zplits »

Sir thanks for the response. I think we should make things up to its proper place and format. Don't mind the image of the screenshot of the main inventory. I think we should change it and focus more on details. What i mean is that, we should group the information according to what it is.

Will it be a good idea if i create a page only for inputting all ingredients?
then another page for inputting all menu, then another page for sales, and another for purchases?

What do you think sir? Coz the previous setup was confuses me, and i know it also confuses you. :) We need to plan first what will be the pages and it's contents, will it be good to go?
I'm online. Thanks
User avatar
Stryks
Forum Regular
Posts: 746
Joined: Wed Jan 14, 2004 5:06 pm

Re: help in DSS

Post by Stryks »

I'll jump into chat and get you going on with some work.

I'll also post back here with some revisions and a description of how the data is going to flow through.

You're going to need a form / section for managers to define ingredients and define menu items for a start. Then a purchases form and an end of day sales form. Lastly of the forms is an inventory / stocktake form. Then you'll have a reporting section to output the data back to the manager as an overview.

That reporting section will be the basis for what you will use on the CEO site forecasting reports.
User avatar
zplits
Forum Contributor
Posts: 158
Joined: Sun Aug 03, 2008 8:59 pm

Re: help in DSS

Post by zplits »

Here is the database tables:

Code: Select all

-- phpMyAdmin SQL Dump
-- version 2.11.6
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Aug 31, 2008 at 11:56 AM
-- Server version: 5.0.51
-- PHP Version: 5.2.6
 
SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
 
--
-- Database: `manag3rs`
--
 
-- --------------------------------------------------------
 
--
-- Table structure for table `tbl_ingredient`
--
 
CREATE TABLE `tbl_ingredient` (
  `I_PK` int(11) NOT NULL auto_increment,
  `code` varchar(8) NOT NULL,
  `ingredientName` varchar(50) NOT NULL,
  `measure_unit` int(11) NOT NULL,
  `item_units` int(4) NOT NULL,
  PRIMARY KEY  (`I_PK`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
 
--
-- Dumping data for table `tbl_ingredient`
--
 
 
-- --------------------------------------------------------
 
--
-- Table structure for table `tbl_inventory`
--
 
CREATE TABLE `tbl_inventory` (
  `I_ID` int(11) NOT NULL,
  `TRANS_ID` int(11) NOT NULL,
  `quantity` int(8) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
 
--
-- Dumping data for table `tbl_inventory`
--
 
 
-- --------------------------------------------------------
 
--
-- Table structure for table `tbl_menu_item`
--
 
CREATE TABLE `tbl_menu_item` (
  `MI_PK` int(11) NOT NULL auto_increment,
  `R_ID` int(11) NOT NULL,
  `itemName` varchar(100) NOT NULL,
  `cost` decimal(9,2) NOT NULL,
  PRIMARY KEY  (`MI_PK`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
 
--
-- Dumping data for table `tbl_menu_item`
--
 
 
-- --------------------------------------------------------
 
--
-- Table structure for table `tbl_menu_item_ingredient`
--
 
CREATE TABLE `tbl_menu_item_ingredient` (
  `MI_ID` int(11) NOT NULL,
  `I_ID` int(11) NOT NULL,
  `units` decimal(9,1) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
 
--
-- Dumping data for table `tbl_menu_item_ingredient`
--
 
 
-- --------------------------------------------------------
 
--
-- Table structure for table `tbl_restaurant`
--
 
CREATE TABLE `tbl_restaurant` (
  `R_PK` int(11) NOT NULL auto_increment,
  `restoName` varchar(50) NOT NULL,
  PRIMARY KEY  (`R_PK`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
 
--
-- Dumping data for table `tbl_restaurant`
--
 
 
-- --------------------------------------------------------
 
--
-- Table structure for table `tbl_transaction`
--
 
CREATE TABLE `tbl_transaction` (
  `TRANS_PK` int(11) NOT NULL auto_increment,
  `date` date NOT NULL,
  `description` varchar(100) NOT NULL,
  `vendor` varchar(100) NOT NULL,
  `cost` decimal(9,2) NOT NULL,
  PRIMARY KEY  (`TRANS_PK`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
 
--
-- Dumping data for table `tbl_transaction`
--
 
 
-- --------------------------------------------------------
 
--
-- Table structure for table `tbl_transaction_items`
--
 
CREATE TABLE `tbl_transaction_items` (
  `I_ID` int(11) NOT NULL,
  `quantity` int(8) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
 
--
-- Dumping data for table `tbl_transaction_items`
--
 
 
Thanks for the help
User avatar
zplits
Forum Contributor
Posts: 158
Joined: Sun Aug 03, 2008 8:59 pm

Re: help in DSS

Post by zplits »

Sir stryks good day. Can i change the field name and data type in the tbl_ingredients? Specifically the measure_unit? because i tried to display it to the table it displays the selected item value. like for example i have chosen kilogram(s) and save it, when i view it, it displays 1
:) so can i change it into varchar?
User avatar
Stryks
Forum Regular
Posts: 746
Joined: Wed Jan 14, 2004 5:06 pm

Re: help in DSS

Post by Stryks »

As the value will be used elsewhere, and we might be linking it to another resource, you might be better off making a linking table holding all of the types.

tbl_measure_units
- UNIT_PK
- unitName

Then when you're displaying the data, you can link the name in with something like ...

Code: Select all

SELECT I_PK, code, ingredientName, units_per_item, unitName FROM tbl_ingredient Inner Join tbl_measure_units ON UNIT_PK = measure_unit
That also means that in your form you'll need to populate the select box with the data from tbl_measure_units.

Code: Select all

SELECT UNIT_PK, unitName FROM tbl_measure_units
Cheers
Post Reply