SQL Server Performance

database design issue - Please suggest better solution

Discussion in 'SQL Server 2005 General Developer Questions' started by MKB, Jan 24, 2011.

  1. MKB New Member

    Hi,
    I have queries regarding Database design in sql server 2005.
    I have Book Library. I have a master where i am storing different category of writers for eg. Story Writes, Poets, Technical writers, Ficton Writes etc.
    For eg. I have stored Louies David who is a Story writer as well as Poet.
    I want to develop a search for above category for seelcted name or po***te a list box for single category.
    I have also another processes like contract for Books which is in transit but need to store details in Database. for eg Book Contract. It is having status as 'Approved' or 'Transit'. But i Need to store the categories and name of the writes in the database as well.
    I have created Writes Category Table with Metadata for eg,
    1) Table - WriterCategory
    WriterCatID int - not Null
    WriteCategory - int - metadata value ( Binary number for eg. 2) then for next record it will be 4, next will be 16 .. as so on.
    CategoryDescrition - Varchar(50) - for . eg. Story Writer
    2) Table - WriterMaster
    WriterMasterID - int
    WriterName - varchar(50) for eg, Louies David
    Metadata Total - metadata which is selected by user for eg. He is Storywriter + Poet then 2 + 4 = 6 will be stored in this table
    3) WriterMasterDetail
    WriterMasterDetailID - Int
    WriterMasterID - FK of WriterMaster
    Metadata - individual category Number stored in WriterCategory
    From above Table i can po***te list boxes for categories as well as from Name i can find the category of the selected name.
    But when I want to this to be also get values for BooKContract then I should i stored the value. Should i go with same Masterdetails Table ?
    The above design was to reduce table for each categories and redunant data. I want to maintain the same thorugh out the application ? I will have to store each CategoryID with WriterMasterID for each contract and will have to retrive with query which gives with multiple records along with metadata. I reralised that i will have to first find the distinct name and then iterate for type of metadata to store BookContract.
    Can u please suggest ? Do I need to stored each WriterMasterID for each category as column in table? Please suggest better solution.
    Thanks in Advance.
  2. Adriaan New Member

    The use of bit-wise comparison is a clever programming technique, but unfortunately in a database it is counterproductive because the Metadata column won't benefit from having an index, and therefore all your filtering will be slow (if not now, then wait until the number of writers starts to rise).
    So I would suggest to have a properly normalized setup, with a Writer table, a Category table, and a WriterCategory table.

Share This Page