Ok, so I am a student, and have VERY limited experience with databases, but I am trying to build the tables for a Shipping company for an assignment. Currently I have The following 10 tables [with attributes] (it will not allow me to post an image):
Table1
======
Customer[PK - CustId, CustAddressId(FK from table "Address"), CustBusName,....],
Table2
======
PU_Man [PK - PUManId, PUDate, PUComments, PUPackageId (FK from table "Package"),
PUCustId (FK from table "Customer"), PUDTID (FK from table DriverTruckRef)]
Table3
======
Del_Man [PK - DelManId, DelDate, DelComments,
DelPackageId (FK from table "Package"),
DelCustId (FK from table "Customer"),
DelDTId (FK from table DriverTruckRef)]
Table4
======
Truck [PK - TruckVin#, TimeLoggedOut, .....],
Table5
======
Driver [PK - DriverId, DriverFN, DriverLN,
DriverRoute# (FK from table "Route"),....]
Table6
======
DriverTruckRef [PK - DTId, DriverId (FK from table "Driver")]
Table7
======
Address [PK - AddressId,
AddressCustId (FK from Customer table),
Address1, Address2, City, State, Zip]
Table8
======
Package [PK - PackageId, PackagePUCondition,
PackagePUProblems, PackageDelCondition, PackageDelProblems]
Table9
======
Route [PK - RouteId, AddressId (FFK from table "Address")]
Table10
=======
Payment [PK - PaymentId,
PaymentCustomerId (FK from customer table),
PaymentPackageId (FK from "Package" table),
PaymentType, PaymentInfo, PaymentAmt,
PaymentAddressId (FK from table "Address")]
I know this is probably the easiest scenario out there for anyone who is experienced, but I am hoping someone can just rip this piece to shreds so that I can see the big picture. Before the shredding of my pride begins, I know the 1:1/n:m/1:m/etc relationships are incorrect, but I need a visual representation of the tables, and Access is what I had to go with. The PK/FK is more the focus.
Also, I am thinking maybe I need to break down the Payment table as well.
Any criticism and advice would be much appreciated.
Maybe this will get you started - every line is a table and the table details are omitted, focusing on keys
Customer
Customer*-Addresses*
Addresses
Packages
Destinations[Addresses,Packages]
DeliveryLog[Package,Driver,Route]
DeliveryAttemptLog[...]
Trucks
Trucks*-Drivers*
Drivers
Hubs
Hubs*-Trucks*
Hubs*-Drivers*
Locations[Packages,Hubs]
Loadings[Packages,Trucks]
Routes
Routes*-Addresses*
Packages*-Routes*
It can get pretty complicated depending on the flexibility you want.