Table Advice for Manifests, Items, and contents

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...

Bill
Bill NelsonITAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PatHartmanCommented:
You have defined a 1-m.  Are you saying you have 1-m-m?  Why wouldn't the pieces from the bin be entered as items?  You would move

Item_Types_ID_FK,  FK to t_Item_Types, Number (For example: Envelope, Box, Mail Bin)

 and a couple of other attributes To the Manifest table.  All the things about the delivery that are common would go into the manifest table and the attributes that are unique to the  individual pieces goes into the child table.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
John TsioumprisSoftware & Systems EngineerCommented:
You only add the a column that contains the Items_ID
0
Bill NelsonITAuthor Commented:
ok... thanks for the advice !
0
Bill NelsonITAuthor Commented:
Thanks so much Pat!
0
PatHartmanCommented:
You're welcome
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
schemas

From novice to tech pro — start learning today.