Can someone please help me create a data base for a shipping compa

[Jon]

Hello everyone

I always run across post from people asking questions but they don’t have
enough detail in them, I will try my best to put as much detail and explain
my self to minimize confusion . Excuse me if I over explain my situation to
the point of been annoying.

Reason for been here:
I am trying to help my parents build a database. they own a small
International shipping company. the service they offer is NOT a "to door
service" which means they DONOT deliver directly to the persons home this
makes it a lot easier to create a database for them. I came here to reach out
to the people that use this everyday that have experience creating databases
maybe you guys can shine some light in the directions I need to go.


From this point on I will refer to the company as “Shipping Companyâ€
I am going to simplify the way the shipping company works to help everyone
understand me better

there is only 4 locations:
2 in the ContryUSA, we can call them USLocation1 and USLocation2
2 in â€CountryX†we can call them CXlocation1 and CXLocation2

This is an example of the way the shipping company works:
On Monday customers(the sender) walks in to USLocation1 and drops a package
to be send to CXLocation1. At the end of the week all the packages that were
dropt off at USLocation1 and USLocation2 are collected and put in a container
and then send to CountryX. A day later the package arrives at CXLocation1
where it can be pickup by the recipient. The recipient picks it up. End of
transaction. Simple right?


My goal is:
Record customers: ex) name, phone#, Location they use.
Record Packages: Package#, sender, recipient, Origin, destination, drop off
date, pickup date
Record Containers: ex) Container#, packages transported in that container.
Record Locations: ex) location#, name, tell# , fax# ,address

Reasons for recording such data:
Customers: Simply to have a record of the customer. also when the Package
details are been entered I want to be able to pick from the pool of customers
for the “sender†and “recipient†fields to prevent wrong data entry.

Packages: To have a record of the package. Also to be able to relate a
packages to a specific customer.

Containers: to keep track of what was in the container

Locations: To have records of locations. Also when the package details are
been entered I want to bee able to pick from a pool of locations for the “
Origin†and “destination†fields to prevent wrong data entry.


So I created some tables
tblCustomers
tblPackages
tblContainers
tblLocations
http://i377.photobucket.com/albums/oo220/jonagpa/Tables.jpg

Problems I ran into:
I stared creating relationships and then I notice that I couldn’t relate
fields the way I wanted to:
One of the problems I have is creating a “one to many†relationship between
one primary key(customers table) and two or more foreign keys(Packages table)
I created this video to help explain my self. Please view it and let me know
if you have a solution.
youtube video:

Note: please give me input of how to create the database as well on how to
solve the relationship problem I ran into

Thank you for you input

 

[Lou]

        Hello everyone

I always run across post from people asking questions but they don’t have
enough detail in them, I will try my best to put as much detail and explain
my self to minimize confusion . Excuse me if I over explain my situation to
the point of been annoying.

Reason for been here:
I am trying to help my parents build a database. they own a small
International shipping company. the service they offer is NOT a "to door
service" which means they DONOT deliver directly to the persons home this
makes it a lot easier to create a database for them. I came here to reachout
to the people that use this everyday that have experience creating databases
maybe you guys can shine some light in the directions I need to go.

From this point on I will refer to the company as “Shipping Company”
I am going to simplify the way the shipping company works to help everyone
understand me better

there is only 4 locations:
2 in the ContryUSA, we can call them USLocation1 and USLocation2
2 in ”CountryX” we can call them CXlocation1 and CXLocation2

This is an example of the way the shipping company works:
On Monday customers(the sender) walks in to USLocation1 and drops a package
to be send to CXLocation1. At the end of the week all the packages that were
dropt off at USLocation1 and USLocation2 are collected and put in a container
and then send to CountryX. A day later the package arrives at CXLocation1
where it can be pickup by the recipient. The recipient picks it up. End of
transaction. Simple right?

My goal is:
Record customers: ex) name, phone#, Location they use.
Record Packages: Package#, sender, recipient, Origin, destination, drop off
date, pickup date
Record Containers: ex) Container#, packages transported in that container..
Record Locations: ex) location#, name, tell# , fax# ,address

Reasons for recording such data:
Customers: Simply to have a record of the customer. also when the Package
details are been entered I want to be able to pick from the pool of customers
for the “sender” and “recipient” fields to prevent wrong data entry.

Packages: To have a record of the package. Also to be able to relate a
packages to a specific customer.

Containers: to keep track of what was in the container

Locations: To have records of locations. Also when the package details are
been entered I want to bee able to pick from a pool of locations for the “
Origin” and “destination” fields to prevent wrong data entry.

So I created some tables
tblCustomers
tblPackages
tblContainers
tblLocationshttp://i377.photobucket.com/albums/oo220/jonagpa/Tables.jpg

Problems I ran into:
I stared creating relationships and then I notice that I couldn’t relate
fields the way I wanted to:
One of the problems I have is creating a “one to many” relationship between
one primary key(customers table) and two or more foreign keys(Packages table)
I created this video to help explain my self. Please view it and let me know
if you have a solution.
youtube video:

Note: please give me input of how to create the database as well on how to
solve the relationship problem I ran into

Thank you for you input

I watched the video twice. I believe you are trying to establish
referential integrity between [Receiver ID] and [Customer ID] and
between [Sender ID] and [Customer ID].

The problem is that you are going the wrong direction.

In SQL one might say:

Alter table tblPackages
alter column [Receiver ID] long integer
constraint Receiver_FK foreign key references tblCustomer.[Customer
ID];

and

Alter table tblPackages
alter column [Sender ID] long integer
constraint Sender_FK foreign key references tblCustomer.[Customer ID];

This tells the database that there should be no sender and no receiver
that is not also a customer. By implication, the customer cannot be
deleted when packages exists for which he is the sender or for which
he is the receiver.


Try to establish the relationships by starting with the tblPackages
table. Draw the line from [Receiver ID] to the tblCustomer.[Customer
ID]. Draw a second line from [Sender ID] to tblCustomer.[Customer
ID].