newbie need help pls, inventory case, kinda long... [Archive]

ray31

05-16-02, 17:21

Hi everyone,
I'm newbie in database and need help in creating database for my project at school.
I plan to use Ms Access.
The case is to create a database for a company that has 3 branches (A,B,C) located in a very small
geographic location (at the same street). The branches are selling the same goods, electronic stuff.
The goal of the database is to create database for the products and enabling real-time updates of goods
delivered between the branches. Each branch can view another branches inventory in real time.
I plan to use centralized database (Client-Server) because distributed database seems too complicated for me.

The tables needed are (my initial thought):
Note: The _No are number from paper associated with it.
ex: Mov_No is number comes from the paper use to move between branches)

1 Product Table (Product_ID, Category_ID, TradeMark_ID, Type, Q_InStock Base_Price, Description)
The example of this table: 1100 Television Konka K1418C3 5 Rp.820.000 14" tv, mono
2 Categories Table (Category_ID, Category_Name)
3 TradeMark Table (TradeMark_ID, TradeMark_Name)

4. Arrival Table (AR_ID, Date, Supplier_ID, Location_Arrival, DeliverOrderNo)
5. Arrival Detail Table (AR_ID, Product_ID, Quantitiy)
6. Supplier Table (Supplier_ID, Supplier_Name, Contact_Name, City, Telp, Mobile_Phone)

7. Sales Table (Sales_ID, Date, Invoice_No, Cust_Name, Cust_Address, Cust_Phone)
8. Sales Detail Table (Sales_ID, Product_ID, Quantity, Sale_Price_Each)

9. Movement_Table (Mov_ID, Date, Mov_No, Loc_Destination)
10. Movement_Detail (Mov_ID, Product_ID, Quantity)

Queries:
1. Inventory in Each Location, sort by Category_ID, TradeMark_ID)
2. Sales Total in Each Location, sort by date
3. Profit Total in Each Location, sort by date

Question:
1. There's still lot of weakness in this table especially the inventory part, can I use use tables like this?
Product Table (Product_ID, Category_ID, TradeMark_ID, Type, Base_Price, Loc_A_Quantity, Loc_B_Q, Loc_C_Q, Description)
Is it not normal? Any suggestion?
2. Should I try to use distributed database instead? What is the criteria?
3. Can any one suggest or give examples for this case

That's all, sorry kinda long, it's still far from complete yet, I know...
I expect any suggestion, comments, etc, all is welcome, thanks in advance

BradEJacobson

07-08-02, 14:45

Question:
1. There's still lot of weakness in this table especially the inventory part, can I use use tables like this?
Product Table (Product_ID, Category_ID, TradeMark_ID, Type, Base_Price, Loc_A_Quantity, Loc_B_Q, Loc_C_Q, Description)
Is it not normal? Any suggestion?

It would not be normal form to put three location fields in the product table. A new location would require a new field. Especially since this is for school, I would think you would want a location table, a product table without a quantity on hand and a inventory table which is an intersection between the product and location table that says how many items are on hand of each product at each location.

Furthermore, it would seem that the arrival table should include a location foreign key to indicate which location is about to experience an increase in inventory when the shipment gets posted. Similarly the sales table should have a foreign key to indicate which location is about to experience a decline in inventory.

2. Should I try to use distributed database instead? What is the criteria?

You are correct to judge that a distributed database is much more of a challenge than a consolidated database. The criteria for distributed is simply "only if you have to", although that is not much of an answer. You do not have to here. Add an adendum to your database design that says it will be deployed with an internet front end. This means all three locations simultaniously hit the same consolidated database over the existing phone network, aw the wonders of an advanced infustructure.

3. Can any one suggest or give examples for this case

I don't know the details of your school assignment, but it seems to me that the transfer of inventory between two locations, needs two locations in the master table, a Origin and destination location. That is two foriegn keys to the location table. It also may take some status fields, requested timestamp (destination location mananger), approved time stamp ( origin location manager ), general status (approved, denied, in route, received but not posted, and posted).

Hope all this helps some, and good luck. I personally had a lot of fun learning all this stuff in the first place back in school, I hope you are having a similar experience.