Address Types (multiple address's)

[Beauford]

I’m a newbie, and have gone round in circles to sort this out. I have a working database, but I know it’s all wrong!
Basically I have a bride who hires my car.
She has a correspondence address, maybe a pickup address, and maybe a second pickup address, a marriage address, and a reception address.
At the moment I have a table for each type of address. (I know this is wrong but I don’t know how to work this.)
How do I have a single table for all address types, and retrieve them for my sales table and invoice.
And show…
Miss Smith
Wedding date
Address
Pickup address
Second pickup address (or not)
Marriage address
Reception venue address
Plus other information, cost etc.
Thanks in advance, any help would be much appreciated

 

[namliam]

just have your normal address table and add a column "address description" or "address type" if you can predefine them. Then you can have anything from 2 addresses... pickup & wedding too 100 stops to make all across the country(side).

 

[Beauford]

My problem is each address could be more than one type. i.e. Correspondence, home, second, reception.
I would need a form for the driver to receive his itinerary from.

 

[namliam]

Which is a problem because?
You can easily have 10 addresses the same with different types.... I can imagine perhaps you want the Correspondence address AND the Pickup address seperate even if they are the same.
I can also very well imagine you should have a "Copy address" button so you can add the same address to a different type if you so desire.

Perhaps even if you have different (fixed) address types you can have some types that are "Car" and some that are "Administration" or something along those lines so you can filter out easily which addresses should go to the driver and which shouldnt. In addition perhaps you might need a seperate "order of visit" table where you can record something like: Bride home (pickup parents) - venue - Bride Home (pickup brother and family) - venue - Bride home (pickup bride) - wait for wedding - Take Couple to Picture location - venue - Take parents to Party location - venue - etc.
That way you only would need the venue one time but have the driving schema display it 100 times if needed. Could even include estimated minutes for the leg so you can try and match the program to the routing and see if they match.

Even for big (oracle) erp and crm systems it is quite common to have Billing address, service address, delivery address to be entered all in one table just with an additional column that is "Address type" (or something along those lines)

 

[Beauford]

Thanks Namliam,
Does that mean each address should have several extra columns, in case that address is used for several types i.e.
H(home)
P(pickup)
MV(marriageVenue)
R(reception)... etc

 

[pr2-eugin]

I would say NO. Create a new table that will store addresses with their corresponding type.

tblClients
clientID (PK)
firstName
surName

tblAddress
addrID
clikentID_FK
addrType
addLine1
addLine2
postcode

So your table might hold data like.

clientID    firstName    surName
1            Leo        Manuel
2            Paul        Eugin

addrID    clikentID_FK    addrType    addLine1               addLine2    postcode
1            1            Home        2 Thicket Avenue      Bristol        BS16 4DH
2            1            Corr        115 Stapleton Road    Bristol        BS5 0PR
3            2            Home        544 Fishponds Road    Bristol        BS16 3DD

 

[namliam]

No, I wouldnt do that.... at all that goes against any and all normalisation conventions.
Instead you would have a table named i.e. AddressType
which holds basiccaly 4 columns
SomeID (autonumber)
AddressID (the key to the address table)
AddressType (probably filled by an dropdownlist populated from another table listing the Types of adresses
AddressOrder (denoting in which order the addresses needs to be visited)

Edit: PR2's faster posting makes me realize offcourse you also need CustomerID (of some sort) in this table.

This will also keep you from repeating popular venues like City Hall, Amsterdam or City Hall Rotterdam or simular popular places.
You could also add a column Distance, where you can enter the KMs/miles to this address from the previous so you can (easily) calculate the total distance travelled (in case of a per KM/Mile fee for example)

In keeping the order as 1,2,3,4,5,6 will allow you to easily "loop back" the previous address though might make re-ordering a bit harder than using 10,20,30,40,50,60 .... where if you get a new address you could simply add it as 25 and splice it between 20 and 30 but you cannot loop them back that easily.

 

[Beauford]

Thanks for that, I know my database is NOT normalised, and I have to type in an address on several forms, sometimes repeating information, so I am going to change it all with your advice. Although I am 64 years old I am still enjoying learning from you all.
Attached is a copy of my present DB relationship, which with your advice I hope to get right

 

[namliam]

Typicaly you wouldnt record invoicing and payment details together with the customer data either.

Typicaly you would have an invoice (table) holding the amount due etc.... and a payments table for individual payments ... the sum of invoices - the sum of payments would be the balance.
I guess in your case it is unlikely for any customer to come for (m)any return purchases so perhaps this design could work but atleast make the balance a calculated field instead of a hard column

 

[Beauford]

As I take a deposit, and then the balance a month before the wedding, would this do?

TblInvoice
InvoiceID (pk)
BrideID (fk)
TotalAmount
Balance

TblPayment
InvoiceID (fk)
Deposit
Discount
FinalPayment

And then a query for the calculated balance???

 

[namliam]

A deposit is usualy taken as payment before the invoice is made right?

You are thinking to much in Excel ways (in columns) databases work in rows....
There is from normalization point of view only one type, one column, Payment
This payment is "related" to the customer, in this case your BrideID (what if the couple is 2 men and there is no "Bride" or what if the groom takes care of arranging the car? or the mother in law, or the nephew of the friend of the uncle of the groom? my point perhaps the name BrideID might be a "bad" idea)

Any payment taken before the invoice should simply be deducted off the invoice or be represented perhaps as a negative cost on the invoice details as should be the Discount given on the invoice...
If the total invoice then is 1.000 euro, and the customer/bride pays in 10 payments for 10 months in 100 installments, you book the 100 euro each time and the calculated balance should then decrease accoordingly.

 

[Beauford]

Good points.. I will give it all a try
Thanks again
Bob