Question Help with structure of new database please

[PhoenixCouriers]

Hi all,

First of all, I am fairly new to this and still learning day by day so please go easy on me and would prefer if anybody can help me this rather then quote "see this link"

( please note that there will be only 1 user and that is me )

First of all, i would like to explain a little about how we will use this data.

I run a small courier/despatch company and want to create a database for adding bookings/jobs for my customers on a daily basis.
These booking are for collections and deliveries for our customer own goods from their office or warehouse to a number of given address's around the UK.

These jobs are then entered into our database and are all invoiced on the last day of each month, so there would be many deliveries and collections for each customer and each invoice.

I need to create a relationship and normalize the Database between these table and fields and would like it if somebody could suggest the best way to put these together.

Normally each booking will have a seperate order number for each booking ( only if customer uses them, if not then simply "Verbal" )

Here are the main table and fields that I will be working with:



CUSTOMERS
AccountNumber
AccountName
AccountsAddress ( this is ware Invoice will be posted Head Office )
Contact1
Telephone1
WarehouseAddress ( this is where their warehouse address is should it not be their Head Office )
Contact2
Telephone2

INVOICE
InvoiceNumber
AccountNumber
InvoiceDate
InvoiceDueBy ( 30 days from Invoice Print Date)
InvoicePrintedOn
InvoiceStatus ( i.e. Processing, Posted, Paid )
PaymentDate

ORDERS
BookingNumber
CollectFrom
Address1
DeliverTo
Address2
CustomerOrderNumber ( customers purchase order number if they use one )
Net
Vat
Total

MANY MANY THANKS

 

[CBrighton]

Looks good so far, but how is the Orders table connected to the others?

I can see the AccountNumber link between the others.

If a single invoice can contain many orders I'd link them like that as a one to many relationship.

You may want an Addresses table with AccountNumber as the foreign key linking to the customers table with fields to hold each line of the address and a field to indicate what type of address it is (i.e. warehouse or office). This would let you easily store addresses used for each customer so they do not have to be input each time, but would also allow you to add new addresses for a customer easily.

 

[PhoenixCouriers]

ok many thanks, i will play about with it a bit more

simon

 

[gemma-the-husky]

going on from what CB said - how does the order table link to the invoice table?

you need to create a sort of chain that links the tables in a logical fashion.

now - the orders tables is interesting - since a single order may presumably lead to several invoices. (only you know - as its your company)

but the generic solution is to say

1 customer had several orders
each order contains several orderlines
now possibly each orderline may result in several delivieries
and deliveries are combined to produce invoices

so depending how complex your system actually is ... thern you may need tables for these (and maybe others)

customers
orders
orderlines
deliveries
invoices

the thing is, if you don't design it all in at the beginning, when you get an order that needs this structure - you can't handle it.

at the very least, maybe you get a single order from a customer for a whole weeks/months collections - which will end up having many items on it.

 

[CBrighton]

It's a good idea to get a selection of the most complex orders / invoices which you have received and have them with you while you are designing the table structure.

If you build it based on how basic orders work it could fall over when you try a complex one and you may need to redevelop it.

 

[PhoenixCouriers]

I have now adjusted the database slightly and was wondering if you could have a look and see if this is Ok.

You will notice that the customers details are all contained in 1 table and this is fine the way are customers table works so do not need to have adjusted as this is how the old one was created and has been fine for over five years so would just like to leave that as it is.

The main areas I need to look at is the invoice and invoice details tables.


many thanks


simon