• Status: Solved
  • Priority: High
  • Security: Public
  • Views: 38
  • Last Modified:

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 Nelson
Bill Nelson
  • 2
  • 2
1 Solution
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.
John TsioumprisSoftware & Systems EngineerCommented:
You only add the a column that contains the Items_ID
Bill NelsonITAuthor Commented:
ok... thanks for the advice !
Bill NelsonITAuthor Commented:
Thanks so much Pat!
You're welcome
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now