Indexing Nested Tables

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.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Aneesh RetnakaranDatabase AdministratorCommented:
Which database ? SQL server 2000 / 2005 ?
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
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)

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

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


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

meerkat2040Author Commented:
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.
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.

meerkat2040Author Commented:
So you're saying that your original query wouldn't work?

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



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
meerkat2040Author Commented:
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?
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.