Question Help with new project for transport database please

[hullstorage]

Hi all
I am trying to tidy up my existing database and create a new one but i dont want to make the mistake and errors i did with this one so was wondering if somebody could help in create the right format and relationships etc for the table below.

I will also explain a little on how the database will work.

Right here goes!

I run a small courier/transport company where our customers request deliveries and collections on a daily basis, these are then entered into the database on a daily basis and then invoiced at the beginning of the following month so a giving customer could have anywhere from 1 job upto 50 jobs each month.

Some customers will have a default collection address which differs from their invoice/accounts address so we would collect a parcel from their warehouse address and invoice thier accounts address.

All the collections we make from our customers will normally differ where the parcel/boxes are being delivered to, here are the tables i will be unsing below..

I may need to adjust things or add fields, any help would be much appreciated..

Thanks all

Simon


TblUKPostcodes ( this table has all th uk postcodes in it that will update
the address fields in other tables below )
PostcodeID
Postcode
Address


TblCustomers
CustomerID
AccountName
Postcode
InvAddress ( accounts/invoice address )
CollectFrom ( this is their warehouse address )
Address ( this is their warehouse address )

TblDeliveries
CustomerID
DeliveryID
CollectFrom ( this will be defaulted to their warehouse address )
Postcode
Address ( this will be defaulted to their warehouse address )
Consignee
Postcode2
Address2
NoofItems
ServiceRequired
CustomerOrderNo
£Cost

TblInvoices
InvoiceID
InvoiceNumber
InvoiceDate
DeliveryID
CustomerID
InvoiceStatus ( i.e. is invoiced/printed )

 

[jzwp22]

Taking a look at your structure, I see a couple areas where you can make some changes. First, it sounds like a customer can have many addresses (one-to-many relationship), so it would be best to have a separate but related table to hold all of the addresses. You can add a field to that table to distinguish the address type (delivery address, collection address, invoicing address etc.)


tblAddressTypes
-pkAddressTypeID primary key, autonumber
-txtAddressType

Your customer table

TblCustomers
-CustomerID
-AccountName

The table to hold all of the customer addresses

tblCustomerAddresses
-pkCustAddressID primary key, autonumber
-fkCustomerID foreign key to tblCustomers
-txtAddress
-fkAddressTypeID foreign key to tblAdressTypes



A table to hold the transaction types (delivery, collection etc.)

tblTransactionTypes
-pkTransTypeID primary key, autonumber
-txtTransType

Now relate the parcel transactions (deliveries, collections) and addresses, similar to what you had for your delivery table.

tblParcelTransactions
-pkParcelTransID primary key, autonumber
-pkCustAddressID foreign key to tblCustomerAddresses
-fkTransTypeID
-NoofItems
-ServiceRequired
-CustomerOrderNo
-£Cost

You would then relate your invoices to the transaction (each transaction is tied to the address and thus to the corresponding customer so you do not need the customerID field)

TblInvoices
-InvoiceID
-InvoiceNumber
-InvoiceDate
-fkParcelTransID foreign key to tblParcelTransactions
-InvoiceStatus ( i.e. is invoiced/printed )