Relating Tables

[Jabirali]

Dear All,
I am new to database design.


Please help me to relate/redesign the following tables.

I am trying to create a simple warehouse database. The items will be stored in different warehouses.These items are for different projects(each project has subprojects too). The items are received in huge boxes (One box may contain different items for different projects).

The objective is to track which items are in which warehouse and in which boxes and tobe queried project wise too.

The Tables are

tableitem
Itemcode
ItemName
ItemDesc
ItemDate
Qty
Warehouseid
Boxid
projectid

tableProject
ProjectId
ProjectName
ProjectSub

I am confused whether i should create tables for WAREHOUSE and BOX.

Please help

[HMEpartsmanager]

Off the cuff here, but I think you would want a Warehouse table and a Box table. Warehouse table can have the different warehouses you have. The Box table can have the different items that came in the box. I am assuming that a Box will have many item codes in that box. Then you can link the these tables with the 2 tables you propose above. It seems appropriate.

BOX has ITEMS for PROJECT and these are stored in different WAREHOUSES.

The only other thing I see that could be another table is the SubProject. Not knowing your data, your PROJECTS could have many SUBPROJECTS.

Some friendly suggestions.

Tim

[orange]

You may get some ideas for data structure from this free data model
http://www.databaseanswers.org/data_...ries/index.htm


Here is an article I think you should read
http://forums.aspfree.com/attachment...2&d=1201055452

[sjs94704]

Here is my suggestion of the tables you should start with on your project. You should learn all you can about linking Primary Key fields to foreign key fields so you can establish your proper relationships between these tables. Once you have accomplished that step you can begin building your forms for the data entry. Be careful !! With you suggested goal of tracking multiple items in multiple warehouses for multiple projects, things can very easily start to get a bit crazy.

ItemT
ItemID
DateEntered
ItemDecription

ItemTransactionT
ItemTransactionID
ItemTransactionDate
ItemID
ProjectID
SubProjectID
WarehouseID
Quantity

ProjectT
ProjectID
ProjectName

SubProjectT
SubProjectID
ProjectID
SubProjectName

WarehouseT
WarehouseID
WarehouseName
Address1
Address2
City
State
ZIP

[Jabirali]

Thank u very much for your replies. SJS, Tim & orange, I will begin with the ideas and structure you have given. Linking the tables is a problem right now, anyway I am learning.
My Tables based upon your Tips will be

Table Item
Itemcode
ItemName
ItemDesc
ItemDate
Qty
Warehouseid
Boxid
projectid

ProjectTable
ProjectID
ProjectName
SubProject Name
Itemcode


WarehouseT
WarehouseID
WarehouseName
ItemCode
BoxId

BoxT
Boxid
BoxNumber
ItemCode
ProjectID