addresses. Single fileld v multiple fields

[Foorboy]

A very good day to all. I'm a 52 year old self taught so far so please be gentle with me hehe. Im designing a database for a small carpet shop. Eventually I will have tables for Suppliers, Customers, products, payments and orders which I believe are the entity types.

I've looked for days on the internet to try to determind the best way to handle address fields. Do I use a textbox with the address free written on several lines or do I have to define single fields, ie. appartment, building, number street town etc........ There are a lot of possible fields to cater for very different address details. does the way I shape this now affect how I will be able to search or print lables later and what about empty fields if I go down the muliple route.

Lastly, should I simply have an address table and not duplicate fields in the supplier & customer tables.

I appreciate any help as I've been stuck on this for ages.

 

[stopher]

Do I use a textbox with the address free written on several lines or do I have to define single fields, ie. appartment, building, number street town etc........ There are a lot of possible fields to cater for very different address details. does the way I shape this now affect how I will be able to search or print lables later and what about empty fields if I go down the multiple route.

Address1
Address2
Address3
Town
County
PostCode
Country

The benefit of splitting is that you can format very easily. Also, its easy to search on part of the address e.g. postcode. And it's pretty easy to write and expression to ignore the blank fields. Searching multiple fields is also an easy task. I won't go into these things here as they deserve there own topic and in any case there's plenty on there in these forums.

Lastly, should I simply have an address table and not duplicate fields in the supplier & customer tables.

This is a more challenging question. There's pros/cons for combining Suppliers/Customers. It depends a lot on how similar suppliers and customers are. Are they both simply contacts with a flags to identify a supplier/customer? Or are you storing a significant amount of information about a supplier that means that it no longer looks like a customer? Can a supplier also be a customer?

Also note that suppliers/customers may have more that one address. You may fit a carpet at one address but invoice another address. So regardless of whether you decide to split customers and suppliers, you may still wish to have a single table called Addresses with a field called AddressType (Delivery Address, Invoice Address etc) as well as the foreign key to the contact and obviously the address details.

hth
Chris

 

[floorboy]

Thankyou Chris, I get it by generalising the names of the first few address fields you allow for unusual addresses and only define the latter constant ones.

Also thanks for using the word "Flag" as that was what I was thinking. I can now have Retail, Trade, Supplier, Reps, Staff, as flags all on the same address table. I'll deal with nulls when I get a bit nearer the time when I've populated the database a bit more.

Regards
Mike

 

[stopher]

Yes, first lines are for those elongated addresses. It's worth looking at the format used by online shops as that can give you a guide. Some label the first line "House name/number & Street".

You might find this interesting. Note that "County" should not be used as part of the postal address in the UK. However, we store it because it is a useful geographic tool e.g. list all customers in Hampshire.

We use the postal town a lot for searches - hence Town has a distinct field. This (hopefully) stops people putting the town in Address3.

hth
Chris

Edit: forgot to add link. Now added.

 

[gemma-the-husky]

although technically splitting the address is better, for modest size enterprises, i just tend to have a single 255 character address field, with maybe a separate additional location field.

you often find that muti-line addresses are not maintained well, and data gets in the wrong column. or even there arent enough sections - so you set out with good intentions, that become a little hazy over time.

The PO address structure is a good indication of how hard normalising addresses really is.

 

[stopher]

although technically splitting the address is better, for modest size enterprises, i just tend to have a single 255 character address field, with maybe a separate additional location field.

You may want to read the link in my last post (which I forgot to put in). But the idea of having to search a whole address string for a postcode doesn't seem sensible to me. Suppose I want to search for PO (Portsmouth). If I search a single string then I'm going to get results with PO in any part of the address. What if I want to search for London addresses. Using a single string I'm going to end up with lots of places in "London Road" for instance.

you often find that muti-line addresses are not maintained well, and data gets in the wrong column. or even there arent enough sections - so you set out with good intentions, that become a little hazy over time.

That doesn't actually matter too much. As long as the address is correct and entered in the right order that you will always be able to print the correct address on a label for instance.

Nevertheless, I do think it's important for the postcode to be entered into the correct field. It's not difficult to test for a valid postcode (at least for most cases).

Chris

 

[gemma-the-husky]

Chris, If an app needed to use post codes separately, I would add an extra post code field.

As I say, if you have an app where you don't need to search address fields, then I just feel you may as well have it all in a single field.