Proper design for address table

[Mike Hildner]

Not sure if this is the right forum, closest I could find.

Are there any accepted norms/standards for designing a table to hold street addresses? This is for the US. I've seen lots of software do it all sorts of ways, and none seems to be perfect. I would like to conform to a standard if one exists.

Thanks,
Mike

[brucevde]

I doubt if there is a standard layout, "typical" would be a better word. It depends on the needs of the application and how normalized do you want to make it?

[TalonSoftware]

Let's see.... you have a street address, sometimes two, a city, a state, a zip (and maybe a zip+4)..... how many other way can it be done?

[Mike Hildner]

In particular, I should be able to match an address to ESRI type files - .shp files used in ArcView and in my particular case MapObjects 2.3. I have the layout of those files. Really, though, that's not what I'm wondering about.

For example, in our current application, our addresses look like

Block
Street Direction
Street Name
Street Type
Apartment Number
City

e.g. 101 N Main St #5 Angel Fire

All of these columns make up the unique constraint. Not perfect, I know, should probably have State (and maybe Zip Code), since you can have many cities with the same name. But this does work for our app because at most, one client will deal with only a county or two.

But this design does not really cut it - our Block is numeric - which is good for sorting etc., but people are asking we allow for an address such as "101 1/2 N Main St". In addition, some cities don't have "101 N Main", but "101 Main N". So I'm thinking about a street prefix and a street suffix.

Do you have any examples of a layout? Or any suggestions for design?

Thanks,
Mike

[Mike Hildner]

I'm thinking this so far

Column_name                                 
--------------------------------------------
Address_Guid                                
Address_Block                               
Address_Block_Suffix                        
Address_Street_Prefix                       
Address_Street_Name                         
Address_Street_Suffix                       
Address_Street_Type                         
Address_Apartment_No                        
Address_City_Guid       Points to City table

Any thoughts appreciated.

[mendhak]

ZIP

[Mike Hildner]

Yeah, I was thinking that the city table consists of a Guid, City Name, State and Zip. Since that would get repeated many times, thought it would be a good idea to point to a record in another table, rather than repeat that info in the address table.

Thanks,
Mike

[szlamany]

We have used a standard for 25 years for address info and it consists of:

CareOf
AptNo
Complex
POBox
Lot
Street
City
State
Zip

We use MS SQL Server, so we have a UDF to build the address print lines.

If Complex is blank, we put "APT " text in front of APTNO value, otherwise the APTNO value gets put in front of COMPLEX data.

If POBOX is not blank, it becomes "PO BOX " + the value of the field.

Lot and Street get put together with a space.

This has worked very well for us.

[Mike Hildner]

Thanks for the info. You don't take into account street direction or street type? e.g. 101 N Main St vs. 101 S Main Ave?

Thanks,
Mike

[szlamany]

For a REAL ESTATE TAX System that we have we do have direction separated from street. That is more or less because we get data from the state and county and that's the way they break it down.

For our school district, payroll and labor union app's we use the format I gave you. For the school district software we validate the street name against a street name table - and "S MAIN ST" is simply a different street than "MAIN ST".

We see no benefit in breaking the "ST or AVE" portion off of the street name - it's really part of the name and sorting and grouping people by it sometimes is needed.

We would never create a city table - that's taking "normalization" to a point where it hurts - and I don't need the pain.

[Mike Hildner]

That's interesting. I can see the point about the street suffix - Ave or St, because it is a different street, guess we do that to limit the street names the user has to choose from. For us, N Main and S Main need to be seperated, because, for example, maybe you want to find all the accidents that happened on Main St, or just on S Main St.

Being a newbie at database design, I'm very interested when you say 'that's taking "normalization" to a point where it hurts'. In our current app, we do not have cities in a different table, but (for some reason), I thought that would be a good idea. Although from my reading I can't tell whether that's 1st or 2nd normal form. Would you know? Care to expand on why that's not a good idea?

Like I said, I don't know much about database design, so any insight would be appreciated.

Thanks,
Mike

[szlamany]

I can see from your NM (New Mexico) location that the S MAIN ST and MAIN ST behave as one long street - I've done work all over the US and I can appreciate that distinction. In the North East (I'm from Connecticut), N MAIN ST and MAIN ST would most likely be on different sides of town...

Normalization is a huge point of debate among DB designers. There are actually 6 normal forms - fourth, fifth and sixth are so hard to explain and so not-real-world that most people never get to them...

We go by the philosophy that the data needs to be easy to look at and approach. Having a CITY table that has two columns - a CITYID and a CITYNAME, and storing the CITYID in the address table makes it impossible to look at the ADDRESS Table and see what the city is. You still have the "same value" for everyone living in EAST OSHKOSH, but it's a number and not a text value. It might take less space to store, but disk space is cheap and disks are fast. In my opinion that goes a step too far and has no real-life benefit - actually creates more of a headache when you want to get an address and city for a person (need to join).

Here's an example that might make the point. We have a table that stores the marks a kid gets in a class in high school. There are 4 marking periods - thus 4 marks. We have 4 fields MP1MARK, MP2MARK, MP3MARK and MP4MARK (there are actually more - final mark, semester average - but not needed to make this point). Some DB designers would make a CLASSMARK table that would store a row for each of these 4 marks. But they are distinctly different things - they are not all MARKS - they are for different marking periods - so we argue that they need to be all in the same ROW of a CLASS table - not a CLASSMARK table.

And to go a step further in our arguement - we already have 50000+ rows a year for each high school in a district for just the CLASS Table (5000 kids with 10 classes each). Adding a CLASSMARK table would be 200000 rows. We store our data forever - so quadrupling the number of rows for what we see as no gain is 3NF going to far.

I've see college admin systems with 500 tables - all with just a couple of columns each. Probably would get an A+ from a professor in NORMAL FORM design - but failed in the real world.

[Mike Hildner]

I grew up in Danbury Small world. Ever go to the Danbury Fair (before they razed it and built a mall, that is)? They used to give us a day off of school to go to that fair.

Anyway, thanks for the info. Gives me something to chew on. I'm in the process of re-writing our app to .NET, and we've definately had some problems because the DB design was never even thought of. So I'm trying to read up on normalization and at least follow some methodology. Of course I have no idea what I'm doing. Any good books on "Normilization for the Real World"?

Regards,
Mike

[szlamany]

Yes - I've been to the Danbury Fair...

As far as good books - WROX publications has good books on SQL, but the chapters on normalization are not very lengthy though. Maybe someone else has some suggestions.

Good luck!