Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Multiple Address Lines

Discussion in 'Information Technology' started by Dee, Jul 28, 2006.

  1. Dee

    Dee
    Expand Collapse
    Guest

    When designing a database table, is it a bad idea to have multiple address
    fields such as Address 1, Address 2 etc. Most of the addresses in my database
    will be at least 2-3 lines. Should the whole address go into just one field
    called address? The addresses are in an excel file that has Address 1 to
    Address 4. They are addresses from all over the world and many of them are
    very long.

    Thanks very much for your help.

    Best regards,

    Dee
     
  2. Loading...


  3. Rick B

    Rick B
    Expand Collapse
    Guest

    If it were me, I'd use five fields...

    Address1
    Address2
    City
    State
    ZIP


    If you take a look at any of the templates that ship with Access, I believe
    that is how they do it. I think most of them also have a country field, but
    I rarely need that for my applications.



    --
    Rick B



    "Dee" <Dee@discussions.microsoft.com> wrote in message
    news:2556E4CA-BDEB-4D76-B659-EC49B5339530@microsoft.com...
    > When designing a database table, is it a bad idea to have multiple address
    > fields such as Address 1, Address 2 etc. Most of the addresses in my
    > database
    > will be at least 2-3 lines. Should the whole address go into just one
    > field
    > called address? The addresses are in an excel file that has Address 1 to
    > Address 4. They are addresses from all over the world and many of them are
    > very long.
    >
    > Thanks very much for your help.
    >
    > Best regards,
    >
    > Dee
    >
     
  4. Ron2006

    Ron2006
    Expand Collapse
    Guest

    Are there also fields for City, State, Zip?
     
  5. Dee

    Dee
    Expand Collapse
    Guest

    The 4 address lines are only parts of the street address. I do have a field
    for City
    State, Country. Should I divide the address into Address 1, Address 2,
    Address 3 etc. and the City, State etc.

    Best regards,

    Dee

    "Rick B" wrote:

    > If it were me, I'd use five fields...
    >
    > Address1
    > Address2
    > City
    > State
    > ZIP
    >
    >
    > If you take a look at any of the templates that ship with Access, I believe
    > that is how they do it. I think most of them also have a country field, but
    > I rarely need that for my applications.
    >
    >
    >
    > --
    > Rick B
    >
    >
    >
    > "Dee" <Dee@discussions.microsoft.com> wrote in message
    > news:2556E4CA-BDEB-4D76-B659-EC49B5339530@microsoft.com...
    > > When designing a database table, is it a bad idea to have multiple address
    > > fields such as Address 1, Address 2 etc. Most of the addresses in my
    > > database
    > > will be at least 2-3 lines. Should the whole address go into just one
    > > field
    > > called address? The addresses are in an excel file that has Address 1 to
    > > Address 4. They are addresses from all over the world and many of them are
    > > very long.
    > >
    > > Thanks very much for your help.
    > >
    > > Best regards,
    > >
    > > Dee
    > >

    >
    >
    >
     
  6. Dee

    Dee
    Expand Collapse
    Guest

    Yes, I have field for city, Province/State Postal Code, Country. The
    spreadsheet that hold the data for my table came from Europe and some of the
    street addresses are very long. The spreadsheet is set up as Address 1,
    Address 2 Address 3 Address 4
    City, State, Country

    Thanks again for your help.

    Best regards,

    Dee
    "Ron2006" wrote:

    > Are there also fields for City, State, Zip?
    >
    >
     
  7. Ken Sheridan

    Ken Sheridan
    Expand Collapse
    Guest

    Dee:

    Definitely, but if you import these into a single table it will not be
    properly normalized. Strictly speaking a fully normalized table would
    probably only include columns for the property (name or number) and the
    StreetID as once you know the SteeetID you'd know the City the State and the
    Country via the relationships between the main table, a Streets table, a City
    table, A States table and a Countries table. In the UK tables frequently
    have only the PostCode and Property columns as here the PostCode tells you
    the street (in fact usually a part of the street, e.g. in my street there are
    two post codes, one for each side). This system is not universal, however.

    In reality few people would go that far in normalizing a table of addresses
    and would be more likely to have in the main table a few AddressLine columns
    plus a CityID column and a PostCode column. The CityID would reference the
    primary key of a Cities table, which would include a foreign key column
    referencing the primary key of a States table (in international databases
    this is usually given a more generic name such as Region, though even that is
    not completely satisfactory as in some countries Region does have a specific
    geo-administrative meaning). This table then includes a foreign key
    CountryID referencing the key of a Countries table.

    Some small countries might not have a regional or federal structure of
    course, so if a city can't be assigned to a region the link in the chain
    between Cities and Countries is broken. The way to handle this is to include
    a N/A region in the Regions table for each country with no regional
    structure. The N/A regions for each different countries will have a
    different RegionID primary key value which will be referenced as the foreign
    key in Cities, and a foreign key CountryID so the link between each city and
    its country is maintained even where there is no regional structure.

    So while the first step would be to import the data into a single table. To
    normalize this and eliminate redundancy the table should then be decomposed.

    I've posted a demo of various means of handling data input for this sort of
    data at:


    http://community.netscape.com/n/pfx...yMessages&tsn=1&tid=23626&webtag=ws-msdevapps


    It uses the English local administrative areas of Parishes, Districts and
    Counties, but the principles are the same for any hierarchically structured
    areas.

    Ken Sheridan
    Stafford, England

    "Dee" wrote:

    > The 4 address lines are only parts of the street address. I do have a field
    > for City
    > State, Country. Should I divide the address into Address 1, Address 2,
    > Address 3 etc. and the City, State etc.
    >
    > Best regards,
    >
    > Dee
    >
     
  8. Ron2006

    Ron2006
    Expand Collapse
    Guest

    IF they are already separated, then I would leave them separated.
    Definitely city, Province/State Postal Code, Country.

    The others I would tend to leave saparated also. Just because you have
    the information, some day they are going to ask you to put it onto a
    label or formletter to fit an envelop window, and you will pay dearly
    trying to separate it then because you have this humongus line that
    won't fit in those locations.

    There is many a developer that would count their blessings if the data
    they received was already separated as your's is.

    That is my feeling. It is a whole lot easier to stick the pieces to
    gether in a query if someone somehow wants it all run together. It is
    murder to try to break it up if it is not broken up.

    Ron
     

Share This Page