Skip to content
  • Categories
  • Recent
  • Tags
  • Popular
  • World
  • Users
  • Groups
Skins
  • Light
  • Cerulean
  • Cosmo
  • Flatly
  • Journal
  • Litera
  • Lumen
  • Lux
  • Materia
  • Minty
  • Morph
  • Pulse
  • Sandstone
  • Simplex
  • Sketchy
  • Spacelab
  • United
  • Yeti
  • Zephyr
  • Dark
  • Cyborg
  • Darkly
  • Quartz
  • Slate
  • Solar
  • Superhero
  • Vapor

  • Default (No Skin)
  • No Skin
Collapse
Code Project
  1. Home
  2. Database & SysAdmin
  3. Database
  4. Database design issue - Pl Sugget a better way

Database design issue - Pl Sugget a better way

Scheduled Pinned Locked Moved Database
databasesql-serverdesignsysadminhelp
3 Posts 3 Posters 0 Views 1 Watching
  • Oldest to Newest
  • Newest to Oldest
  • Most Votes
Reply
  • Reply as topic
Log in to reply
This topic has been deleted. Only users with topic management privileges can see it.
  • M Offline
    M Offline
    meerabhuva
    wrote on last edited by
    #1

    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, Fiction 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 selected name or populate 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 populate 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 realised 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.

    L P 2 Replies Last reply
    0
    • M meerabhuva

      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, Fiction 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 selected name or populate 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 populate 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 realised 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.

      L Offline
      L Offline
      Lost User
      wrote on last edited by
      #2

      meerabhuva wrote:

      The above design was to reduce table for each categories and redunant data.

      Apply normalization techniques, and you'll get the correct design.

      meerabhuva wrote:

      WriteCategory - int - metadata value ( Binary number for eg. 2) then for next record it will be 4, next will be 16 .. as so on.

      Why did you skip 1 and 8? Your datatype says integer, how many items could you store in there? It looks like a running value btw, are you sure you don't want an Identity?

      meerabhuva wrote:

      Metadata Total - metadata which is selected by user for eg. He is Storywriter + Poet then 2 + 4 = 6 will be stored in this table

      Table: WriterKind
      Id Caption
      1 Storywriter
      2 Poet
      3 Scriptwriter

      Table: WriterMaster
      Id Name WriterKind
      1 Berton Braley 2

      It might be a flagged-enum in your code, but that doesn't make it a good idea to store it that way in a relational database.

      I are Troll :suss:

      1 Reply Last reply
      0
      • M meerabhuva

        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, Fiction 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 selected name or populate 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 populate 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 realised 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.

        P Offline
        P Offline
        PIEBALDconsult
        wrote on last edited by
        #3

        meerabhuva wrote:

        WriteCategory - int - metadata value ( Binary number for eg. 2)

        That's one way, but it will limit you to thirty-two categories. Better to have a Category table and allow many-to-many relationships with Books and Authors. Category ID Description 1 Mystery 2 Romance 3 Sci-Fi Author ID Name 1 Douglas Adams AuthorCategory AuthorID CategoryID 1 3 <-- Douglas Adams + Sci-Fi Book ID Title 1 The HitchHikers... BookCategory BookID CategoryID 1 3 <-- The HitchHikers... + Sci-Fi

        1 Reply Last reply
        0
        Reply
        • Reply as topic
        Log in to reply
        • Oldest to Newest
        • Newest to Oldest
        • Most Votes


        • Login

        • Don't have an account? Register

        • Login or register to search.
        • First post
          Last post
        0
        • Categories
        • Recent
        • Tags
        • Popular
        • World
        • Users
        • Groups