Northwind Mystery

#1 (**permalink**) 28-Jul-2006, 08:02 AM

In the Northwind database, there are several indexes for which I do not
understand the purpose.

Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/information-technology/9472-northwind-mystery.html
In the Products table, there are indexes on CategoryID and SupplierID. For
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=9472
what purpose are these created? I haven't seen anywhere they are used. If
there is a reason for them, I'd like to know.

These are indexes of surrogate key values that are the autonumbers in other
tables.

Thanks.

Tom Ellison

*

#2 (**permalink**) 28-Jul-2006, 08:02 AM

Those fields (CategoryID, SupplierID) are the foreign keys, which, along
with primary keys, are used to establish relationships between tables.
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=9472
Primary/foriegn key is a very basic concept of relational database. One must
understand these basic things in order to do some meaningful work with
relational database system.
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=9472

"Tom Ellison" wrote in message
news:uyaZi5UdGHA.1656@TK2MSFTNGP02.phx.gbl...
> In the Northwind database, there are several indexes for which I do not
> understand the purpose.
>
> In the Products table, there are indexes on CategoryID and SupplierID.
> For what purpose are these created? I haven't seen anywhere they are
> used. If there is a reason for them, I'd like to know.
>
> These are indexes of surrogate key values that are the autonumbers in
> other tables.
>
> Thanks.
>
> Tom Ellison
>
>

#3 (**permalink**) 28-Jul-2006, 08:02 AM

Dear Norman:

OK. I won't recite my credentials, but I'm a senior developer, and I'm
extremely familiar with these concepts. There MUST be a unique index on the
column(s) of a relationship on the ONE side of the relationship. But why
index them on the MANY side of the relationship? They certainly aren't
unique there!

I have generally not seen an index on the related columns placed in the
table on the MANY side!

I suppose my question presumes some familiarity with this common database
example.

Thanks for offering your suggestion, Norman!

Tom Ellison

"Norman Yuan" wrote in message
news:OzQ0wbVdGHA.1204@TK2MSFTNGP02.phx.gbl...
> Those fields (CategoryID, SupplierID) are the foreign keys, which, along
> with primary keys, are used to establish relationships between tables.
> Primary/foriegn key is a very basic concept of relational database. One
> must understand these basic things in order to do some meaningful work
> with relational database system.
>
> "Tom Ellison" wrote in message
> news:uyaZi5UdGHA.1656@TK2MSFTNGP02.phx.gbl...
>> In the Northwind database, there are several indexes for which I do not
>> understand the purpose.
>>
>> In the Products table, there are indexes on CategoryID and SupplierID.
>> For what purpose are these created? I haven't seen anywhere they are
>> used. If there is a reason for them, I'd like to know.
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=9472
>>
>> These are indexes of surrogate key values that are the autonumbers in
>> other tables.
>>
>> Thanks.
>>
>> Tom Ellison
>>
>>
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=9472
>
>

#4 (**permalink**) 28-Jul-2006, 08:02 AM

On Thu, 11 May 2006 17

13 -0500, "Tom Ellison"
wrote:

>In the Products table, there are indexes on CategoryID and SupplierID. For
>what purpose are these created? I haven't seen anywhere they are used. If
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=9472
>there is a reason for them, I'd like to know.
>
>These are indexes of surrogate key values that are the autonumbers in other
>tables.

They're not "surrogate" key values - they are foreign keys. For
example, there is an enforced Relationship defined between the primary
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=9472
key of Categories and the corresponding foreign key CategoryID in the
Products table; the way that Access enforces this relationship is...
tadaa!... with an Index.

John W. Vinson[MVP]

#5 (**permalink**) 28-Jul-2006, 08:02 AM

Dear John:

An autonumber used for a relationship between two tables is certainly what
I'd call a surrogate key. If you have another definition, I'd be glad to
here it. But that's not the point here.

The question is, why would there be an index on the CategoryID column in the
Products table, that is, in the table on the MANY side of the relationshkp.
I know there must be a unique index on the key column on the ONE side of the
relationship. I have created many one-to-many relationships, but having an
index on the key column on the MANY side has never been needed. Don't you
concur?

Which brings me back to the question. Why index the CategoryID and the
SupplierID in the table on the MANY side of the relationshkp, the Products
table?

Thanks.

Tom Ellison

"John Vinson" wrote in message
news:7rl762do6j7oc7vbinbrp1anjj93nud70a@4ax.com...
> On Thu, 11 May 2006 17

13 -0500, "Tom Ellison"
> wrote:
>
>>In the Products table, there are indexes on CategoryID and SupplierID.
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=9472
>>For
>>what purpose are these created? I haven't seen anywhere they are used.
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=9472
>>If
>>there is a reason for them, I'd like to know.
>>
>>These are indexes of surrogate key values that are the autonumbers in
>>other
>>tables.
>
> They're not "surrogate" key values - they are foreign keys. For
> example, there is an enforced Relationship defined between the primary
> key of Categories and the corresponding foreign key CategoryID in the
> Products table; the way that Access enforces this relationship is...
> tadaa!... with an Index.
>
> John W. Vinson[MVP]

#6 (**permalink**) 28-Jul-2006, 08:02 AM

"Tom Ellison" wrote in message
news:uyaZi5UdGHA.1656@TK2MSFTNGP02.phx.gbl...
> In the Northwind database, there are several indexes for which I do not
> understand the purpose.
>
> In the Products table, there are indexes on CategoryID and SupplierID.
> For what purpose are these created? I haven't seen anywhere they are
> used. If there is a reason for them, I'd like to know.

Well, you are correct, they are not really needed, and the database will
function without them.

However, if you plan run any report based on products, and want to select
(filter) only particular products of a given category, then a index would
make a very large difference in performance. The same idea applies to the
CatagryID.

If I filter, or want to find a particular product by catagoryID, then a
index would again make a VERY large difference in performance.

As a side note, any time you create a field name of number type, and the
field name ends with "ID" , ms-access will default to indexing that column
(so, actually, you have to be careful to not accident create indexes when
you don't need them!)

I have to say for the most part, that indexing these types of "catgalogry"
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=9472
fields is a very good idea from a performance point of view.

So, likely, they are there because when you enter field names that end in
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=9472
id...ms-access will automatic default to indexed (so, perhaps someone did
nothing here (lazy), .and just let ms-access default to having a index).

However, from a sorting, or selecting point of view...having a index here
will make a large difference in performance....

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal@msn.com
http://www.members.shaw.ca/AlbertKallal

#7 (**permalink**) 28-Jul-2006, 08:02 AM

Dear Albert:

Now, that's some good stuff! Thanks!
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=9472

I certainly didn't know Access would create indexes based on column names.
That seems weird indeed. But it probably explains why these indexes are
there. Whoever built Northwind may not have known there were there.

Isn't it true that having extra indexes can also be a bad thing? It takes
time to change the index whenever a new row is added, a row is deleted, or
when the subject column is updated. This is NOT so good for performance.

Your suggestion that this column might well be filtered in a query or form
is quite reasonable. The amount of performance gained with an index would
depend on what proportion of the table is filtered. If a filter includes
80% of the table, then a table scan is not so inefficient - you're going to
have to read 80% of the rows anyway. If the filter includes 1% of the
table, an index could be a big boost. Makes sense here.

To put this into Northwind without any explanation doesn't tend to serve the
rationale behind Northwind, to teach and give examples. I could not learn
this from Northwind, but fortunately there is Albert.

Now, the question is, are these indexes used somewhere in Northwind,
justifying their existence? Well, you might come along and filter by just
about any column in the database, right? So, let's start out with all the
columns indexed, just for good measure. Somehow, that's the impression I'm
getting here. Not really a good idea.

I'll be sure none of my numeric columns ends in ID. That's for sure. I
like to control the design of my tables, not have it taken from me.

Tom Ellison

"Albert D.Kallal" wrote in message
news:urVU89VdGHA.2188@TK2MSFTNGP05.phx.gbl...
> "Tom Ellison" wrote in message
> news:uyaZi5UdGHA.1656@TK2MSFTNGP02.phx.gbl...
>> In the Northwind database, there are several indexes for which I do not
>> understand the purpose.
>>
>> In the Products table, there are indexes on CategoryID and SupplierID.
>> For what purpose are these created? I haven't seen anywhere they are
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=9472
>> used. If there is a reason for them, I'd like to know.
>
> Well, you are correct, they are not really needed, and the database will
> function without them.
>
> However, if you plan run any report based on products, and want to select
> (filter) only particular products of a given category, then a index would
> make a very large difference in performance. The same idea applies to the
> CatagryID.
>
> If I filter, or want to find a particular product by catagoryID, then a
> index would again make a VERY large difference in performance.
>
> As a side note, any time you create a field name of number type, and the
> field name ends with "ID" , ms-access will default to indexing that
> column (so, actually, you have to be careful to not accident create
> indexes when you don't need them!)
>
> I have to say for the most part, that indexing these types of "catgalogry"
> fields is a very good idea from a performance point of view.
>
> So, likely, they are there because when you enter field names that end in
> id...ms-access will automatic default to indexed (so, perhaps someone did
> nothing here (lazy), .and just let ms-access default to having a index).
>
> However, from a sorting, or selecting point of view...having a index here
> will make a large difference in performance....
>
>
> --
> Albert D. Kallal (Access MVP)
> Edmonton, Alberta Canada
> pleaseNOOSpamKallal@msn.com
> http://www.members.shaw.ca/AlbertKallal
>
>

#8 (**permalink**) 28-Jul-2006, 08:02 AM

"Tom Ellison" wrote in message
news:OWIn5LWdGHA.3352@TK2MSFTNGP03.phx.gbl...
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=9472
> Dear Albert:
>
> Now, that's some good stuff! Thanks!
>
> I certainly didn't know Access would create indexes based on column names.
> That seems weird indeed. But it probably explains why these indexes are
> there. Whoever built Northwind may not have known there were there.
>
> I'll be sure none of my numeric columns ends in ID. That's for sure. I
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=9472
> like to control the design of my tables, not have it taken from me.

Hi Tom,

Not just ID, Access automatically indexes fields ending in:

ID; key; code; num

unless you disable this in Options.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

#9 (**permalink**) 28-Jul-2006, 08:02 AM

Hi Tom,

> I'll be sure none of my numeric columns ends in ID. That's for sure. I like to control the
> design of my tables, not have it taken from me.

If you go to Tools | Options | Tables/Queries
look for the text box called "AutoIndex on Import/Create:"
You'll see the default settings that Access uses to automatically
create indexes without your permission. On a default installation
you can see that Access has four entries in that text box. Any field
names that end with ID, key, code, or num will be "graciously"
indexed automatically for you.

--
Jeff Conrad
Access Junkie - MVP
http://home.bendbroadband.com/conrad...essjunkie.html
http://www.access.qbuilt.com/html/articles.html

"Tom Ellison" wrote in message:
news:OWIn5LWdGHA.3352@TK2MSFTNGP03.phx.gbl...

> Dear Albert:
>
> Now, that's some good stuff! Thanks!
>
> I certainly didn't know Access would create indexes based on column names. That seems weird
> indeed. But it probably explains why these indexes are there. Whoever built Northwind may not
> have known there were there.
>
> Isn't it true that having extra indexes can also be a bad thing? It takes time to change the
> index whenever a new row is added, a row is deleted, or when the subject column is updated. This
> is NOT so good for performance.
>
> Your suggestion that this column might well be filtered in a query or form is quite reasonable.
> The amount of performance gained with an index would depend on what proportion of the table is
> filtered. If a filter includes 80% of the table, then a table scan is not so inefficient - you're
> going to have to read 80% of the rows anyway. If the filter includes 1% of the table, an index
> could be a big boost. Makes sense here.
>
> To put this into Northwind without any explanation doesn't tend to serve the rationale behind
> Northwind, to teach and give examples. I could not learn this from Northwind, but fortunately
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=9472
> there is Albert.
>
> Now, the question is, are these indexes used somewhere in Northwind, justifying their existence?
> Well, you might come along and filter by just about any column in the database, right? So, let's
> start out with all the columns indexed, just for good measure. Somehow, that's the impression I'm
> getting here. Not really a good idea.
>
> I'll be sure none of my numeric columns ends in ID. That's for sure. I like to control the
> design of my tables, not have it taken from me.
>
> Tom Ellison
>
>
> "Albert D.Kallal" wrote in message
> news:urVU89VdGHA.2188@TK2MSFTNGP05.phx.gbl...
>> "Tom Ellison" wrote in message
>> news:uyaZi5UdGHA.1656@TK2MSFTNGP02.phx.gbl...
>>> In the Northwind database, there are several indexes for which I do not understand the purpose.
>>>
>>> In the Products table, there are indexes on CategoryID and SupplierID. For what purpose are
>>> these created? I haven't seen anywhere they are used. If there is a reason for them, I'd like
>>> to know.
>>
>> Well, you are correct, they are not really needed, and the database will function without them.
>>
>> However, if you plan run any report based on products, and want to select (filter) only
>> particular products of a given category, then a index would make a very large difference in
>> performance. The same idea applies to the CatagryID.
>>
>> If I filter, or want to find a particular product by catagoryID, then a index would again make a
>> VERY large difference in performance.
>>
>> As a side note, any time you create a field name of number type, and the field name ends with
>> "ID" , ms-access will default to indexing that column (so, actually, you have to be careful to
>> not accident create indexes when you don't need them!)
>>
>> I have to say for the most part, that indexing these types of "catgalogry" fields is a very good
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=9472
>> idea from a performance point of view.
>>
>> So, likely, they are there because when you enter field names that end in id...ms-access will
>> automatic default to indexed (so, perhaps someone did nothing here (lazy), .and just let
>> ms-access default to having a index).
>>
>> However, from a sorting, or selecting point of view...having a index here will make a large
>> difference in performance....
>>
>>
>> --
>> Albert D. Kallal (Access MVP)
>> Edmonton, Alberta Canada
>> pleaseNOOSpamKallal@msn.com
>> http://www.members.shaw.ca/AlbertKallal
>>
>>
>
>