SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    eigo hanasemasu ka? Yes. =) ZuulJin's Avatar
    Join Date
    Dec 2001
    Location
    Japan
    Posts
    655
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    View or Lookup Table?

    I am creating an online student/teacher type system. I run into a situation where I cannot decide if I want to use a view or a lookup table. I'm using MS SQL Server 2000 at the moment.

    Interaction story:
    Student is looking for an available teacher. The student clicks on the "Find Teacher" link, which loads a sortable, pageable datagrid (ASP.NET) of available teachers they can select from.

    Data Interaction story:
    Webapp loads the "Find Teacher" page. It then loads a datagrid and queries the database to find all available teachers. Then it binds the data to the datagrid.

    Choice #1: View
    I could setup a view and have the webapp query the view to retrieve a list of available teachers. This view would have the advantage that it would always remain current without a need to 'update it', like a lookup would require. However, in my understanding, a view is slower than a lookup table.

    Choice #2: Lookup Table
    I could setup a lookup table and have the webapp query it to retrieve a list of available teachers. The lookup table has the advantage of speed, but would require the webapp to track changes. For instance, if a teacher who was previously available becomes unavailable, the webapp would have to remove the teacher from the lookup table.

    What choice would you make? And, if you can, please provide some insight into the reasons behind your choice. Also, if there is something I didn't think of, please don't hesitate to add your thoughts.

    Thank you!
    U.S. DoD Member in Japan?
    Choose your base. Buy|Sell. Easy
    @ APO Ads.



  2. #2
    SitePoint Guru asterix's Avatar
    Join Date
    Jun 2003
    Posts
    847
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I could setup a view and have the webapp query the view to retrieve a list of available teachers. This view would have the advantage that it would always remain current without a need to 'update it', like a lookup would require. However, in my understanding, a view is slower than a lookup table.
    Yes, that is why we use views, it gives us the data we already have, prepared for use in certain circumstances.

    Views are not in any way slower than tables. The query analyzer in any case chooses whether to use the view or table at run time, it is only for your convenience that you use views.

    You shouldn't create and populate a new table unless you have a new entity in your logical model, or unless you need a new relationship.

  3. #3
    eigo hanasemasu ka? Yes. =) ZuulJin's Avatar
    Join Date
    Dec 2001
    Location
    Japan
    Posts
    655
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by asterix
    You shouldn't create and populate a new table unless you have a new entity in your logical model, or unless you need a new relationship.
    That line there is magic. I tend to follow that model of thinking as well.

    Thanks.
    U.S. DoD Member in Japan?
    Choose your base. Buy|Sell. Easy
    @ APO Ads.




Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •