locked
Views for Lookup Table RRS feed

  • Question

  • hi,

       I am designing a database. I organized list items are in one table called Lookup table. So i have country, designation etc are in the same table. I user to display my drop down list using type. So i have the following tables

    1. LookupMaster
    ---------------------
    TypeID autonumber<
    TypeName varchar(25)

    2. LookupParamaters
    ---------------------------------
    Id   autonumber
    ParameterName varchar(50)
    TypeID int   ------ foreignkey(TypeID) from LookupMasater

    Here is my design. I studied some perfomance articles and which explained that create views for lookup tables for better performance.

    Can anyone guid me for this? how i create views for this design. or i change any design ?


    • Edited by akhilrajau Tuesday, March 27, 2012 3:20 AM
    Tuesday, March 27, 2012 3:19 AM

Answers

  • Ideally, each object should have a separate TABLE. To acheive that, LookupMaster would need to be split into separate TABLEs, one per type of lookup.

    CREATEing VIEWs for a lookup is generally not a good idea. It is always best to try it in the most straightforward fashion, and only if problems are encountered, to try a possibly faster method. Therefore, i would suggest to not bother with VIEWs and to CREATE separate TABLEs for each object.

    Tuesday, March 27, 2012 12:54 PM
    Answerer
  • Is this a question on how to design the indexed VIEW, or if the VIEW is a good idea?  If the latter, perhaps the SQL Server Database Engine forum would be a better location for your question.

    Denormalization for performance is not my area of expertise.



    Tuesday, March 27, 2012 1:14 PM
    Answerer

All replies

  • I have doubts about performance , please provide sample data + desired result...Are you using SS2008 and on wards?

    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

    Tuesday, March 27, 2012 9:49 AM
  • Hello,

    Please provide some sample data and also mention the sql server version that you will use.

    Tuesday, March 27, 2012 11:28 AM
  • Ideally, each object should have a separate TABLE. To acheive that, LookupMaster would need to be split into separate TABLEs, one per type of lookup.

    CREATEing VIEWs for a lookup is generally not a good idea. It is always best to try it in the most straightforward fashion, and only if problems are encountered, to try a possibly faster method. Therefore, i would suggest to not bother with VIEWs and to CREATE separate TABLEs for each object.

    Tuesday, March 27, 2012 12:54 PM
    Answerer
  • thanks for the answers..I am usning SQL sever 2008

    Actually i read the following link

    http://msdn.microsoft.com/en-us/library/ff647793.aspx

    And the content is...

    Use Indexed Views for Denormalization

    When you have joins across multiple tables that do not change frequently, such as domain or lookup tables, you can define an indexed view for better performance. An indexed view is a view that is physically stored like a table. The indexed view is updated by SQL Server when any of the tables that the indexed view is based on are updated. This has the added benefit of pulling I/O away from the main tables and indexes.

    • Edited by akhilrajau Tuesday, March 27, 2012 12:59 PM
    Tuesday, March 27, 2012 12:58 PM
  • Is this a question on how to design the indexed VIEW, or if the VIEW is a good idea?  If the latter, perhaps the SQL Server Database Engine forum would be a better location for your question.

    Denormalization for performance is not my area of expertise.



    Tuesday, March 27, 2012 1:14 PM
    Answerer
  • Hi Akhilrajau,

    My suggestion would be to go for separate tables, instead of creating a view for each one. It will be easy to maintain also.


    Regards, http://shwetamannjain.blogspot.com

    Tuesday, March 27, 2012 1:35 PM
  • thanks friend. Actually i am using lookup table (which is common) for all the list values instead of separate table. Some of this is user defined also. So i can give only one page to the user for add list of values based on the type.

    Now i am in performance tuning for this database and this is new to me. When i read the articles specified above i got a point that for lookup tables like i explained, use indexed view for better performance. So i asked this question to clear the concept. As of now i didn't use any views for tables and i directly call data from this lookup tables based on the type

    Tuesday, March 27, 2012 1:57 PM