A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

What tables forthis database



 
 
Thread Tools Display Modes
  #1  
Old November 15th, 2009, 06:44 PM posted to microsoft.public.access.tablesdbdesign
Bob H[_4_]
external usenet poster
 
Posts: 161
Default What tables forthis database

I have a excel spreadsheet at work for shipments comming in to the
country for us, and then delivered by a transport company to a said
power station.
Among some of the 17 column headers, there a
Customer
Project
Ship from
Ship to
Ship date
Customs in date
Customs clearance date
Delivery date
Commercial Value
The other column headers are dates of receipt of POD, who signed etc,
and hyperlink cells to documents for the said shipment.

I want to put all thisinformation into a Access 2007 database and have
had sometought on how to do it, with separtate tables for CustomerID,
ProjectID, ShipFrom, ShipTo.
Are, or do I need any more separate tables for this database?

Thanks
  #2  
Old November 16th, 2009, 03:25 AM posted to microsoft.public.access.tablesdbdesign
Allen Browne
external usenet poster
 
Posts: 11,706
Default What tables forthis database

To decide on the best structure, you'll need to identify where the
one-to-many relationships exist within your workflow, e.g.:

Could one shipment contain many items that you need to split up and track
separately?

At the most basic level, I imagine you would want tables like this:

Client table: one record for each company you deal with (consignors,
consignees, shippers, ...), with a ClientID primary key.

ItemType table: one record for each type of item you handle (e.g. turbine,
....), with ItemTypeID primary key.

Shipment table: one record for each incoming or outgoing shipment. Fields:
- ShipmentID AutoNubmer primary key
- ShipDirection Number 1 for inward; -1 for outward
- ClientID Number who from (inward) or to (outward)
- ShipmentDate Date/time date arrived (inward) or sent (outward)
- CarrierID Number who delivered this shipment.

ShipmentDetail table: one record for each item in the shipment.
- ShipmentDetailID AutoNumber primary key
- ShipmentID Number which Shipment record this line applies to
- Quantity Number how many of this itme
- ItemTypeID relates to ItemType.ItemTypeID
- ValueEach Currency how much each item is worth

If that's completely foreign, there's a Northwind sample database for
Access. Open it, and see how the Orders and OrderDetails tables work, and
hopefully the above will make sense.

In tracking customs etc, you will need to consider whether there could be
further one-to-many relationships here, e.g.:

a) Could some items in a shipment be cleared before others (e.g. where some
item-types are sensitive while others are not)? If so, the clearances dates
go in the Shipment Detail table, because clearance could apply to different
items separately.

b) Could there be several steps in a clearance journey? For example, to
export a sensitive electronic part, might it need to clear US customs before
it's allowed out, and then to also clear customs in the target country
before it's allowed in? Could there also be other clearances along the way?
If yes, you'll need another table for the steps of the journey.

Hope that helps you think in terms of the one-to-many relations that will
lead you to a good data schema.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"Bob H" wrote in message
...
I have a excel spreadsheet at work for shipments comming in to the country
for us, and then delivered by a transport company to a said power station.
Among some of the 17 column headers, there a
Customer
Project
Ship from
Ship to
Ship date
Customs in date
Customs clearance date
Delivery date
Commercial Value
The other column headers are dates of receipt of POD, who signed etc, and
hyperlink cells to documents for the said shipment.

I want to put all thisinformation into a Access 2007 database and have had
sometought on how to do it, with separtate tables for CustomerID,
ProjectID, ShipFrom, ShipTo.
Are, or do I need any more separate tables for this database?

Thanks


 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 01:29 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.