Best way to deal with addresses please.

[Sprocket]

Hi.
I have an Access 2000 database running on Windows 2000 Operating System. The database is on a shared drive on a network with approx 20 concurrent users. It runs a bit sluggishly so I am looking for ways to speed things up. The network guys say that the bit rate is OK so I need to tweak the database. I have run the performance and table analysers and get no suggestions for improvement. However, I have spotted a potential problem with the way addresses are stored.

Currently addresses are stored in the person table along with personal stuff like date of birth, gender, ethnicity etc. the address part has six fields:

Oh by the way - this is for the UK so the address structure has to be a bit flexible to accommodate quite a few variations of how addresses can be written but a reasonable guide is:

Number (or house name) and street name
(often a second line for part one of address)
Village / urban area
Town
County
Postcode

Part one of my question is - is this the best way to store this information or should I just gather it like this on a form and then store it as concatenated text in a single field.
I have tried this on a small test database and it works OK and prints address labels etc. - - But is it better or not?

Part two is - should the addresses be stored in the person table at all or should it be in a separate table. This is the bit I cannot get my head round. If it is in a separate table then each record in the table needs to be unique but because of the nature of the client base (i.e. students) many students share accommodation - OK a 1 to many relationship - but what about students in halls of residence they will have an individual room but the remainder of the address is the same. So for say 1000 students in the same hall 95% of the address is repeated although each address is unique overall. And with 4 halls there are 4000 entries that could potentially be reduced to 4 plus a room number and hall name, but I am not at all sure how to achieve this. And then what about addresses for students not in halls but shared houses? I cannot get my head round this. I can see why it was set up the way it was but there must be a better way.

Do I need one table, two tables or six tables (one for each field)?

And how do I then enter data? Do I have to have combo boxes on each line to see if that value has been entered before?

The data gathered would always be entered via forms so I can get the fields together but I don't know how many tables I need nor how best to relate them.


Can anyone suggest how I might get round this problem?

Thanks... Sprocket

 

[FoFa]

If you put the whole address in one field, it makes very hard to query on a part of it. Like, tell us everyone in Allen Hall (halls of residence) as an example. Would not suggest that.
If you put the addresses in a separate table, that would be your best approach. Typically you would just put the address in, in it's own row, then tie it back to the student via some key. But you have the unique circumstance where having your halls of residence you would actually reuse many of the same addresses over and over. So in theroy you could tie the address to the student by the key in the address table, in the student record. So say Room 102 this year belongs to Joe Blow, but next year it belongs to Bob Jones as an example, without entering a new address. This would limit the size of your address table. You would need some date effectivity on the student for this address (trust me).
Down side, is history important? If the address information changes, then basically the same change effects the persons tied to that address for prior years also. I suspect history is not that important based on the current design. I am guessing you don't care John Smith had room 102 last year, and is in room 204 this year. If you did you would track it differently.
Don't create a table for each column, just way to complicated, or overkill.

 

[Bat17]

I would be inclined to leave the table as it is.
Are your users running seperate front ends on their own PC's?

You might want to check out this site.
http://www.granite.ab.ca/access/performancefaq.htm

Peter

 

[Sprocket]

Hi,

Thanks for the feedback- particularly the link to the Access Performance FAQ document. There is a great deal in there that might well be worth looking into.

Regarding users having individual front ends the answer is NO they don't. That was the first thing I tried, splitting the database and setting up individual front ends. Strangely it slowed things down quite significantly so I went back to a single file MDB on the shared drive. Unfortunately this is a third level subfolder but armed with info from the above FAQ document I will start by asking our network manager if I can try splitting the database again and having the back end moved to a higher level folder. This might be significant as most folders have longish names that clearly identify departments but initials would do as well and save significantly on file name length.

Thanks again........ Sprocket

 

[Pat Hartman]

The other thing to do immediately is open each table in design view and set their subdatasheet properties to none.

If you decide to separate the addresses (this will save space since thousands of records have the same address), keep the suite number with the student record and don't forget that when a student's address changes, you can't just change the record he is linked to since the other thousand students linked to that address didn't move along with him.