Address table structure

Is the table structure to store addresses somewhat standardized?
Address1
Address2
City
State
ZipCode
Country
I ask because I am working with international addresses for the first time
and I don't know what to expect in terms of what my users will want to enter.Not really, it varies from database to database. The best thing you can do
is make it somewhat generic and not make any assumptions about any country.
State is specific to US, in Canada they have provinces, other contries have
regions, districts, you name it. I think this structure might be more
generic and applicable to many countries:
Address1
Address2
Address3
City
PostalCode
Region
Country
Of course, address formatting varies from country to country.
Roman
--
Roman Rehak
http://sqlblog.com/blogs/roman_rehak
"Curtis" <Curtis@.discussions.microsoft.com> wrote in message
news:91008C72-8ADB-475D-A64D-7714F5A41124@.microsoft.com...
> Is the table structure to store addresses somewhat standardized?
> Address1
> Address2
> City
> State
> ZipCode
> Country
> I ask because I am working with international addresses for the first time
> and I don't know what to expect in terms of what my users will want to
> enter.|||What are some best practices for data types? Is it best practice to store a
list of countries and states in a separate table and reference the id in the
main address table?
"Roman Rehak" wrote:
> Not really, it varies from database to database. The best thing you can do
> is make it somewhat generic and not make any assumptions about any country.
> State is specific to US, in Canada they have provinces, other contries have
> regions, districts, you name it. I think this structure might be more
> generic and applicable to many countries:
> Address1
> Address2
> Address3
> City
> PostalCode
> Region
> Country
> Of course, address formatting varies from country to country.
> Roman
> --
> Roman Rehak
> http://sqlblog.com/blogs/roman_rehak
>
> "Curtis" <Curtis@.discussions.microsoft.com> wrote in message
> news:91008C72-8ADB-475D-A64D-7714F5A41124@.microsoft.com...
> > Is the table structure to store addresses somewhat standardized?
> >
> > Address1
> > Address2
> > City
> > State
> > ZipCode
> > Country
> >
> > I ask because I am working with international addresses for the first time
> > and I don't know what to expect in terms of what my users will want to
> > enter.
>
>|||Hi
> What are some best practices for data types? Is it best practice to store
> a
> list of countries and states in a separate table and reference the id in
> the
> main address table?
Sure
"Curtis" <Curtis@.discussions.microsoft.com> wrote in message
news:2F00010D-EE81-438E-A0F8-8BA573B57985@.microsoft.com...
> What are some best practices for data types? Is it best practice to store
> a
> list of countries and states in a separate table and reference the id in
> the
> main address table?
> "Roman Rehak" wrote:
>> Not really, it varies from database to database. The best thing you can
>> do
>> is make it somewhat generic and not make any assumptions about any
>> country.
>> State is specific to US, in Canada they have provinces, other contries
>> have
>> regions, districts, you name it. I think this structure might be more
>> generic and applicable to many countries:
>> Address1
>> Address2
>> Address3
>> City
>> PostalCode
>> Region
>> Country
>> Of course, address formatting varies from country to country.
>> Roman
>> --
>> Roman Rehak
>> http://sqlblog.com/blogs/roman_rehak
>>
>> "Curtis" <Curtis@.discussions.microsoft.com> wrote in message
>> news:91008C72-8ADB-475D-A64D-7714F5A41124@.microsoft.com...
>> > Is the table structure to store addresses somewhat standardized?
>> >
>> > Address1
>> > Address2
>> > City
>> > State
>> > ZipCode
>> > Country
>> >
>> > I ask because I am working with international addresses for the first
>> > time
>> > and I don't know what to expect in terms of what my users will want to
>> > enter.
>>sql

Address Searching (sp?)

I have a form that inputs an address (city/state/zip/etc.). Basically I
need to search our database for this address, prior to it being entered.
Problem in that people type St. vs. Street vs. St, Ave. vs. Avenue vs. Ave,
etc. Anyone have an affective algorithm for searching for comparable
addresses? Just curious if someone has run into/solved this problem
effectively.
Thanks,
JamesThe problem has been solved quite effectively by address correction software
vendors. It might be quite a task to write a proprietary set of functions to
do the same thing.
Don't know if this is feasible for you but you could have a separate field
to store the street type and then provide a drop-down list so that the user
doesn't have the option to input a variant (such as Ave. instead of Avenue)
"James" wrote:

> I have a form that inputs an address (city/state/zip/etc.). Basically I
> need to search our database for this address, prior to it being entered.
> Problem in that people type St. vs. Street vs. St, Ave. vs. Avenue vs. Ave
,
> etc. Anyone have an affective algorithm for searching for comparable
> addresses? Just curious if someone has run into/solved this problem
> effectively.
> Thanks,
> James
>
>

Address Searching (sp?)

I have a form that inputs an address (city/state/zip/etc.). Basically I
need to search our database for this address, prior to it being entered.
Problem in that people type St. vs. Street vs. St, Ave. vs. Avenue vs. Ave,
etc. Anyone have an affective algorithm for searching for comparable
addresses? Just curious if someone has run into/solved this problem
effectively.
Thanks,
James
The problem has been solved quite effectively by address correction software
vendors. It might be quite a task to write a proprietary set of functions to
do the same thing.
Don't know if this is feasible for you but you could have a separate field
to store the street type and then provide a drop-down list so that the user
doesn't have the option to input a variant (such as Ave. instead of Avenue)
"James" wrote:

> I have a form that inputs an address (city/state/zip/etc.). Basically I
> need to search our database for this address, prior to it being entered.
> Problem in that people type St. vs. Street vs. St, Ave. vs. Avenue vs. Ave,
> etc. Anyone have an affective algorithm for searching for comparable
> addresses? Just curious if someone has run into/solved this problem
> effectively.
> Thanks,
> James
>
>

Address Searching (sp?)

I have a form that inputs an address (city/state/zip/etc.). Basically I
need to search our database for this address, prior to it being entered.
Problem in that people type St. vs. Street vs. St, Ave. vs. Avenue vs. Ave,
etc. Anyone have an affective algorithm for searching for comparable
addresses? Just curious if someone has run into/solved this problem
effectively.
Thanks,
JamesThe problem has been solved quite effectively by address correction software
vendors. It might be quite a task to write a proprietary set of functions to
do the same thing.
Don't know if this is feasible for you but you could have a separate field
to store the street type and then provide a drop-down list so that the user
doesn't have the option to input a variant (such as Ave. instead of Avenue)
"James" wrote:
> I have a form that inputs an address (city/state/zip/etc.). Basically I
> need to search our database for this address, prior to it being entered.
> Problem in that people type St. vs. Street vs. St, Ave. vs. Avenue vs. Ave,
> etc. Anyone have an affective algorithm for searching for comparable
> addresses? Just curious if someone has run into/solved this problem
> effectively.
> Thanks,
> James
>
>

Addition to the above.

I forgot to add, you'll see that ZONE has less records than STATE, thats
because the current system is storing NAMES not keys, and a ZONE name can be
in more than on STATE, e.g. STATE:"New York" can have a zone called
ZONE:"AGRICULTURE" and STATE:"California" can have a zone called
ZONE:"AGRICULTURE"Sorry, another thing... the record counts in the first post are DISTINCT
counts...

Adding folders under the solution

In RS, can one add a folder under the Reports folder? I don't see where I can do this. We work with many different state's and we would like to organize reports by state.

Thanks for the information.

hi,

Yes, you can. You can do it on the Report Server website (eg: http://reporservername/reports) clicking on the new folder. Then you can set up explicit security on it, if you do not want to have the inherited.

Also you can set up in the VS2005 SSRS project the destination folder in the project properties. It used when you deploy reports in a project. If the folder does not exist, it will create it and inherits the permission from the parent.

Regards,

Janos

|||

What I guess I was hoping to be able to do is to right click in the solution and Add a folder, like I can in a web project. We also use Source Safe and would like a similar directory structure there too. We would like to create one solution with the reports, however each report might vary because of different state requirements. From there, we would like a directory structure of Reports/StateName/thisreport.rdl.

Each state then get a copy of their own reports when the program as a whole is delivered to them. They have no need to see other state reports.

We are trying not to create a new Solution for each state because there are reports that are generic and can be created just once.

|||

You can create your folder structure in a solution. Each folder must have its own project below the solution.

eg.> I'd like a folder structure like this:

\

--US

|

-CA

-WA

Then I create an SSRS solution, then I add 3 projects. 1st project property page set the TargetReportFolder to /US, 2nd /US/CA, 3rd /US/WA.... etc.

I hope it helps.

regrads,

Janos

|||I'm also trying to create a folder structure inside the report project itself, to no avail. I think what guyinkalamazoo3 was really going for is the ability to create folders inside the single report project to keep things organized, without having to add a separate report project for each different state, i.e.:

ReportProject
-Reports (Root directory)
--California (Subdirectory)
--Washington (Subdirectory)

It seems odd that you can add folders to organize your project with any other project type, but you can't organize your reports this way. Was this missed in development of SSRS, or is it just another "feature"?