Database for stock keeping

[uvray90]

Hello guys...

Iam developing a stock maintenance system for a hotel (PHP-MySQL) in which the client will be updating the consumption everyday.

Suppose the items are like

Pepsi
Buns
Sauce

Then everyday consumption he needs to keep track. He will login and enter the consumption details for 23rd March then date and the consumtion has to be stored

date      pepsi    buns    sauce

23rd Mar  30       50      90

This is what i thought..Simple. But he needs to add extra items by his own it seems :-( .Cant figure out how to do. Please suggest some points so that i can get a lead.

Thank you :-)

[Boskor]

Try with such sql table:

example:
id item date quantity
1 pepsi 23rd Mar 30
...

With php you will insert items in this table and he can add what he want (no limits)

use a date format that the db can interpret easily eg. datetime (yyyy-mm-dd) or unix timestamp.

TABLE: SALE_INFO
============
sale_id: PK,int
dated: datetime
user_id: FK,int

TABLE: ITEM_SALES
=================
item_sales_id: PK,int
sale_id: FK, int
item_id: FK, int
qty: int

TABLE: ITEMS (this should be developed further)
============ 
item_id: PK, int
item_name: varchar [50]
reorder_level: int
details: text

TABLE: USER
===========
user_id: PK, int
...etc...

So now he can CRUD (create [add], read [select], update, delete) other users, sale_info, items, individual item sales