I am looking for a sample database or schema for the following situation.
We receive individual mail items and courier packages at our reception desk and shipping department. We also receive buckets of mail which can include hundreds of items.
So, I am thinking that I would set up the following main table relationships, but would like opinions if this is the best way to go about this. This will become a split database shared by 2 operators and an administrator.
I am thinking a one to many relationship for:
(FK = Foreign Key)
t_Manifests (as the one side)
Manifests_ID PK Autonumber
Manifest_Date Date/Time Default = Date()
Manifest Time Date/Time Default = Time()
Created_By FK to t-Users, Number
t_Items (As the many side)
Items_ID PK, Autonumber
Ship/Receive? FK to Action_Types, Number (For Example: Received, Shipped)
Shipped/Received_By_FK, FK to t_Users, Users_ID (Is there a better way to word this based on Ship/Receive choice made above)
Delivery_Agents_ID_FK, FK to t_Delivery_Agents, number
From_Company_FK, FK to t_Companies, Number
From_Instructor_FK, FK to t_Instructors, Number (most of our items come from instructors, but we still receive invoices from companies)
Item_Types_ID_FK, FK to t_Item_Types, Number (For example: Envelope, Box, Mail Bin)
Item_Sizes_ID_FK, FK to t_Item_Sizes, Number (For Example: Regular, Small, Medium , Large)
Addressed_To_ID_FK, FK to t_Users, Number
Statuses_ID_FK, FK to t_Statuses, Number (For Example, Holding, Processing, Delivered)
Current_Location_ID_FK, FK to t_Locations, Number, (For Example: Shipping dock, Reception Desk, Purolator Office)
Now here is where I am wondering what to do...
An Item can have many pieces (Mail Bin)... Do I create another table called t_Contents
Contents_ID PK Autonumber
and then add all of the fields again found in receiving items???
Thank you for any help with this as I am really not sure how to proceed...