?
Solved

Indexing Nested Tables

Posted on 2006-04-27
7
Medium Priority
?
301 Views
Last Modified: 2008-03-17
I've got a beginner's question.  I'm very new to SQL and relational databases.

I understand the unique identifier, but how do I identify a list of unique id's associated with the first id.

For example, let's say I have an invoice number.

That is the unique identifier that gets me to the table of items purchased.  Each of them has a unique identifier.

How do I index this subtable so that when searching for the purchased items, I don't have to search the entire database!

I want to be able to quickly pull up the list of purchased items for that unique invoice.
0
Comment
Question by:meerkat2040
7 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16550649
Which database ? SQL server 2000 / 2005 ?
0
 
LVL 4

Expert Comment

by:nguyenvinhtu
ID: 16550776
I don't really understand your idea, meerkat?
Why have to search the whole database?
You just have to search on the Table which contains purchased items only. As far as I know, after you create a Primary key (consider to be unique), you also have indexed on that columns.
If you got another column and decided that you make index on that column.
Open SQl Server Enterprise Manager /
Browse to your Database /
Choose the table you want to have index /
Right click on that table /
All tasks /
Manage Indexes /
New /
Check to choose column to index + Name your new index + choose cluster, etc. as you like to.
then OK
And now you have new index.

Index help you to query your result faster than usual. You can read more about SQL Index and its beneficial.

Hope you have good starting point on RDBMS
0
 
LVL 44

Accepted Solution

by:
Arthur_Wood earned 400 total points
ID: 16551666
The table of InvoiceDetail Items should be designed so that EACH and EVERY record (which identifies a single deatil item for an Invoice) holds the ID of the invoice to which it belongs.  Then you INDEX the InvoiceDetail table on the COMBINATION of the Primary Key of the InvoiceDetail table AND the InvoiceID (which is a FOREIGN Key in that table)

InvoiceTable
---------------------
InvoiceID           Primary Key
CustomerID       Foreign Keu to Customer Table ( a singel Customer could have multiple Invoices)
InvoiceDate
InvoiceAmount
additional fields for the invoice


InvoiceDetailTable
--------------------------
InvoiceDetailID         Primary Key
InvoiceID                 Foregin Key to Invoice table (a Single invoice could have multiple detail items)
ItemID                    Foreign key to Item Table
ItemQuantity            How many of the Item
ItemCost                 Unit cost of the item


Now, to get the Invoice and ALL of items realted items:

Select * from InvoiceTable INNER JOIN InvoiceDetailTable ON InvoiceTable.InvoiceID =  InvoiceDetailTable.InvoiceID


AW
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:meerkat2040
ID: 16553620
To aneeshattingal, I'm using MySQL/PHP on Windows XP Pro.  That's what my Internet Provider uses on their Linux machines, so I wanted to replicate that environment.

To nguyenvinhtu, thank you.  I suppose it's easier to set up relations in MS SQl Server's GUI.

To Arthur_Wood, thank you.  It's starting to make sense.  So even though I'm not specifying the InvoiceDetailTable.InvoiceDetailID in the query, the fact that it's indexed on both InvoiceDetailID and InvoiceID means that the database will jump right to the InvoiceDetails associated with an InvoiceID without having to search the entire InvoiceDetailTable.

I didn't realize that if you indexed on two columns that you could fetch the correct rows by referencing just one column (InvoiceID).  I had thought that if you indexed on two columns, that you had to specify both columns in the query.  That was confusing, because I didn't know where to store all the InvoiceDetailIDs within the InvoiceTable.

I was beginning to think that I needed a separate table for each InvoiceID that would contain the InvoiceDetails.  That didn't make sense, but coming from a flat file background, that seemed like the only way to make indexing fast.  But of course, it seemed like a terribly unmanageable scenario.

I'm glad to know that I can Index on two columns and fetch the rows that match just one column in an efficient way.

Thank you.
0
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 16554169
I would create two indexes : one on InvoiceDetailID, InvoiceID , and a second on InvoiceID alone.

If you have an index on two columns, but only search on the FIRST column defined in the index, that behaves just like an index on that column alone.  You cannot use the combined index to search on the second column, alone - however.

AW
0
 

Author Comment

by:meerkat2040
ID: 16554942
So you're saying that your original query wouldn't work?

Select * from InvoiceTable INNER JOIN InvoiceDetailTable ON InvoiceTable.InvoiceID =  InvoiceDetailTable.InvoiceID

InvoiceTable:
InvoiceID,CustomerID,PurchaseDate
1,1,4/27/06
2,1,4/27/06
3,2,4/28/06

InvoiceDetailTable
InvoiceID,InvoiceDetailID,ItemPurchasedID,Qty
1,1,25,1
1,2,48,2
1,3,2,1
2,4,15,3
2,5,17,2
2,6,3,5
3,7,5,2

So lets say I wanted select all the items from InvoiceDetailTable where InvoiceID=2.  Are you saying that this won't work because I'm only specifying a single index?

Select * from InvoiceTable INNER JOIN InvoiceDetailTable ON InvoiceTable.InvoiceID =  InvoiceDetailTable.InvoiceID WHERE InvoiceID=2
0
 

Author Comment

by:meerkat2040
ID: 16557918
Oh, I'm getting it now.  The index doesn't have to be unique, but the primary key does.  So the index of InvoiceDetailTable can point to multiple rows based on a single InvoiceID.  

Is that correct?
0

Featured Post

Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Lotus Notes has been used since a very long time as an e-mail client and is very popular because of it's unmatched security. In this article we are going to learn about  RRV Bucket corruption and understand various methods to Fix "RRV Bucket Corrupt…
In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question