Indexing Nested Tables

Posted on 2006-04-27
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.
Question by:meerkat2040
    LVL 75

    Expert Comment

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

    Expert Comment

    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
    LVL 44

    Accepted Solution

    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


    Author Comment

    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.
    LVL 44

    Expert Comment

    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.


    Author Comment

    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

    Author Comment

    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?

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    Introduction After workin in a plethora of programming languages like C, Pro*C, ESQL/C, C++, VC++, VB, Java, HTML,JavaScipt etc, technologies and frameworks like JSP, Servlets, Struts, Spring, IBatis etc and databases like MS Access, SQLServer, Inf…
    Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
    Video by: Steve
    Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    730 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

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now