combine city field with Prov/State

Hello I am new to SQL Server 2005 and am designing my first database.

In the AdventureWorks sample database the city field is included with the street address where it repeats for each row.

Why would you not put it in a separate table with state/province or a separate table all togeather?

Thanks for your help.

Blair:

Basically, because city is a component of an "address entity". Now, you COULD extract the city so that the city was in a separate table from the address table and store the key to the city table as part of the address information. I would try to avoid this.

In many of the applications that I have used one way of locating information associated with a specific person is as part of an "address search." When you perform an "address search" it is much more efficient if all of the data elements for an address are located in the same table. If you have "City" in a table by itself with a key to the "City" table included as part of the "Address" table you would need to join the two tables to included "City" information as part of a filtering criteria as part of a "search by address."

Now, we DO from time to time receive "zipcode files" from the post office. These we DO store on a separate table; however, this particular collection of data serves a completely different function.

You might have a valid reason to have a separate "City" table.

Frequently, we do have a separate "State Code" table.

|||

I can't see any reason to put it in a seperate "table," but a seperate field would be sufficient. It really all depends on how you use the table.

It would be my venture to guess that your company is only concerned with querying records on a state level.

Just my twist on it,

Adamus

|||

In trying to 'normalize' a database, you should continully 'deconstruct' the various parts until the effort to re-construct is more costly than the space savings of deconstruction.

For example, if an address database was 'really' normalized, then there would be a table of street names, a table of street types (Ave, St, Rd, etc.), perhaps a table of directions (N, NNE, NE, SNE, E, etc.) , a purist could even make an extreme case for a table of address numbers. Then a table of Cities, Counties, States, Countries. Imagine a table of address types (Apt, Suite, Mail Stop, etc.)

The original address table could easily be deconstructed until it was nothing more than a table full of FK-PK links. Extreme storage space savings, yet excessive computation resources required everytime you need an address.

A good rule of thumb: Normalize until it 'hurts' and then back up a level until it 'works'.

Third Normal Form is NOT always your friend in this day of very inexpensive storage.

|||

Thank You for all your help!

This is exactly what I wanted to know.

combine city field with Prov/State

Hello I am new to SQL Server 2005 and am designing my first database.

In the AdventureWorks sample database the city field is included with the street address where it repeats for each row.

Why would you not put it in a separate table with state/province or a separate table all togeather?

Thanks for your help.

Blair:

Basically, because city is a component of an "address entity". Now, you COULD extract the city so that the city was in a separate table from the address table and store the key to the city table as part of the address information. I would try to avoid this.

In many of the applications that I have used one way of locating information associated with a specific person is as part of an "address search." When you perform an "address search" it is much more efficient if all of the data elements for an address are located in the same table. If you have "City" in a table by itself with a key to the "City" table included as part of the "Address" table you would need to join the two tables to included "City" information as part of a filtering criteria as part of a "search by address."

Now, we DO from time to time receive "zipcode files" from the post office. These we DO store on a separate table; however, this particular collection of data serves a completely different function.

You might have a valid reason to have a separate "City" table.

Frequently, we do have a separate "State Code" table.

|||

I can't see any reason to put it in a seperate "table," but a seperate field would be sufficient. It really all depends on how you use the table.

It would be my venture to guess that your company is only concerned with querying records on a state level.

Just my twist on it,

Adamus

|||

In trying to 'normalize' a database, you should continully 'deconstruct' the various parts until the effort to re-construct is more costly than the space savings of deconstruction.

For example, if an address database was 'really' normalized, then there would be a table of street names, a table of street types (Ave, St, Rd, etc.), perhaps a table of directions (N, NNE, NE, SNE, E, etc.) , a purist could even make an extreme case for a table of address numbers. Then a table of Cities, Counties, States, Countries. Imagine a table of address types (Apt, Suite, Mail Stop, etc.)

The original address table could easily be deconstructed until it was nothing more than a table full of FK-PK links. Extreme storage space savings, yet excessive computation resources required everytime you need an address.

A good rule of thumb: Normalize until it 'hurts' and then back up a level until it 'works'.

Third Normal Form is NOT always your friend in this day of very inexpensive storage.

|||

Thank You for all your help!

This is exactly what I wanted to know.

Column chart bug?

When I am designing a column chart, the chart looks exactly how I want
it to look in Layout view, but then changes in the Preview mode and
when viewed in Report Manager.
Specifically I want to do this:
In the chart Y axis tab, for the scale options:
Minimum .6
Maximum 1.3
Cross 1
The Layout looks correct - when Previewed the chart keeps starting at
the minimum of 0 (instead of the .6 minimum). Since my chart is to
show variance from 1 (below or above), this bug makes it useless (1 is
no longer centered in view - but is up near 1.3).
Any ideas or workarounds?
Thanks!Update: looks like if I use whole numbers, the chart behaves correctly. So -
I guess the question is, how can I use fractions to specify lower and upper
ranges? If I do go with whole numbers (multiply the data by 10) to get the
chart to look OK, can I change the displayed Y axis labels (by dividing them
by 10)?
"coldfact" wrote:
> When I am designing a column chart, the chart looks exactly how I want
> it to look in Layout view, but then changes in the Preview mode and
> when viewed in Report Manager.
> Specifically I want to do this:
> In the chart Y axis tab, for the scale options:
> Minimum .6
> Maximum 1.3
> Cross 1
> The Layout looks correct - when Previewed the chart keeps starting at
> the minimum of 0 (instead of the .6 minimum). Since my chart is to
> show variance from 1 (below or above), this bug makes it useless (1 is
> no longer centered in view - but is up near 1.3).
> Any ideas or workarounds?
> Thanks!
>|||Try this:
Minimum 0.6
Maximum 1.3
CrossAt 1.0
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"coldfact" <coldfact@.discussions.microsoft.com> wrote in message
news:E16862E2-6EBB-4422-A438-DD7EE8673F3F@.microsoft.com...
> Update: looks like if I use whole numbers, the chart behaves correctly.
So -
> I guess the question is, how can I use fractions to specify lower and
upper
> ranges? If I do go with whole numbers (multiply the data by 10) to get the
> chart to look OK, can I change the displayed Y axis labels (by dividing
them
> by 10)?
> "coldfact" wrote:
> > When I am designing a column chart, the chart looks exactly how I want
> > it to look in Layout view, but then changes in the Preview mode and
> > when viewed in Report Manager.
> >
> > Specifically I want to do this:
> > In the chart Y axis tab, for the scale options:
> > Minimum .6
> > Maximum 1.3
> > Cross 1
> >
> > The Layout looks correct - when Previewed the chart keeps starting at
> > the minimum of 0 (instead of the .6 minimum). Since my chart is to
> > show variance from 1 (below or above), this bug makes it useless (1 is
> > no longer centered in view - but is up near 1.3).
> >
> > Any ideas or workarounds?
> > Thanks!
> >|||Much thanks! It is the 1.0 (vs 1) that makes the difference...
:)
"Robert Bruckner [MSFT]" wrote:
> Try this:
> Minimum 0.6
> Maximum 1.3
> CrossAt 1.0
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "coldfact" <coldfact@.discussions.microsoft.com> wrote in message
> news:E16862E2-6EBB-4422-A438-DD7EE8673F3F@.microsoft.com...
> > Update: looks like if I use whole numbers, the chart behaves correctly.
> So -
> > I guess the question is, how can I use fractions to specify lower and
> upper
> > ranges? If I do go with whole numbers (multiply the data by 10) to get the
> > chart to look OK, can I change the displayed Y axis labels (by dividing
> them
> > by 10)?
> >
> > "coldfact" wrote:
> >
> > > When I am designing a column chart, the chart looks exactly how I want
> > > it to look in Layout view, but then changes in the Preview mode and
> > > when viewed in Report Manager.
> > >
> > > Specifically I want to do this:
> > > In the chart Y axis tab, for the scale options:
> > > Minimum .6
> > > Maximum 1.3
> > > Cross 1
> > >
> > > The Layout looks correct - when Previewed the chart keeps starting at
> > > the minimum of 0 (instead of the .6 minimum). Since my chart is to
> > > show variance from 1 (below or above), this bug makes it useless (1 is
> > > no longer centered in view - but is up near 1.3).
> > >
> > > Any ideas or workarounds?
> > > Thanks!
> > >
>
>|||Explanation: If at least one value of the axis settings is an integer, we
use a mode of the chart control to generate "nice" integer labels and not
default float labels like 0.05, 1.05, 2.05, etc. (which are determined based
on the minimum and maximum data point values of the chart).
If you really want float labels, make sure to either not specify values for
certain axis settings or specify them as float values rather than integer
values.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"coldfact" <coldfact@.discussions.microsoft.com> wrote in message
news:A3D8D209-6C1E-4930-B948-202466FDF30D@.microsoft.com...
> Much thanks! It is the 1.0 (vs 1) that makes the difference...
> :)
> "Robert Bruckner [MSFT]" wrote:
> > Try this:
> > Minimum 0.6
> > Maximum 1.3
> > CrossAt 1.0
> >
> > --
> > This posting is provided "AS IS" with no warranties, and confers no
rights.
> >
> > "coldfact" <coldfact@.discussions.microsoft.com> wrote in message
> > news:E16862E2-6EBB-4422-A438-DD7EE8673F3F@.microsoft.com...
> > > Update: looks like if I use whole numbers, the chart behaves
correctly.
> > So -
> > > I guess the question is, how can I use fractions to specify lower and
> > upper
> > > ranges? If I do go with whole numbers (multiply the data by 10) to get
the
> > > chart to look OK, can I change the displayed Y axis labels (by
dividing
> > them
> > > by 10)?
> > >
> > > "coldfact" wrote:
> > >
> > > > When I am designing a column chart, the chart looks exactly how I
want
> > > > it to look in Layout view, but then changes in the Preview mode and
> > > > when viewed in Report Manager.
> > > >
> > > > Specifically I want to do this:
> > > > In the chart Y axis tab, for the scale options:
> > > > Minimum .6
> > > > Maximum 1.3
> > > > Cross 1
> > > >
> > > > The Layout looks correct - when Previewed the chart keeps starting
at
> > > > the minimum of 0 (instead of the .6 minimum). Since my chart is to
> > > > show variance from 1 (below or above), this bug makes it useless (1
is
> > > > no longer centered in view - but is up near 1.3).
> > > >
> > > > Any ideas or workarounds?
> > > > Thanks!
> > > >
> >
> >
> >