locked
Database design issue RRS feed

  • Question

  • Hello all,

    i have one issue raise while designing database.

    i have one table company master in which company_Id is a primary key

    and all other table almost 27-28 tables i could not able to set primary key on any other table.

    the reason is that while you create new company you create one id per company,but in that company you apply some default data and is also repeatable ,so you can not able to define any other primary key in any table.

    so my database design is as one parent--> all other child.

    the database diagram of my database is as below.

    so i know about any performance related issue raise in future when workin with bulk data?

    please give me answer.

    thanks in advance.

     

     

     

    g only 5-6 tablse) is as below.

    Monday, January 30, 2012 7:09 AM

Answers

  • Hi Sagar,

    I assume most part of activity in your system would be on the company level? In such case I'd suggest you to add identity columns to the child tables and define clustered indexes as (Comp_NG, Id identity) there. There is also the possibility that Comp_NG as the left most column would benefit non clustered indexes if all your queries have Comp_NG as the predicate in where/join clauses.

    As you probably saw I mentioned clustered index, not primary key. Primary key is the logical design concept while clustered index is the physical design. In that solution you can define non clustered primary key either on ID or on other candidate keys in the system as long as those are unique. It's not the bad idea as long as you can live with extra index maintenance in case if you have the usage for this key/index there. In case if you decided not to add this index on ID column, again make sure that all queries have Comp_NG predicate in the query so you don't have table scans.

    Performance wise (Comp_NG, ID) index pattern introduces some extra index fragmentation in compare with clustered index on auto increment values but it would not be the huge one - data is evenly incrementing within the company scope. Same time it could improve some queries and bulk load because queries with comp_ng filter access data that logically located in the same place and bulk load imports the data to the different places in data files (no hot spots due new page/extent allocations with identity).


    Thank you!

    My blog: http://aboutsqlserver.com

    • Proposed as answer by Peja TaoEditor Wednesday, February 1, 2012 3:05 AM
    • Marked as answer by Peja TaoEditor Friday, February 3, 2012 7:46 AM
    Monday, January 30, 2012 2:22 PM
  • Hi Sagar,

    There is no problems to add PK to all the child tables and put Clustered indexes for them these PK rather than the COMP_NO, this will be normal FK for the Parent table.

    Another Slution

    Also you could use the Generalization Concept by put all the tables that has the same columns together in one table and make a one-to-many relationship between the general tables and their child so you will have 3 levels of your relations

    COMP(COMP_NO[PK]) >> HEAD(HEAD_NO[PK], COMP_NO[FK]) >> ACCOHEAD (ACCHEAD_NO[PK], HEAD_NO[FK])

                                                                                   >> BLSHEAD(BLSHEAD_NO[PK], HEAD_NO[FK])

     

    Thanks,

    MOHAMED A. SAKR

    • Proposed as answer by MohamedSakr Thursday, February 2, 2012 3:29 PM
    • Marked as answer by Peja TaoEditor Friday, February 3, 2012 7:46 AM
    Thursday, February 2, 2012 3:29 PM

All replies

  • Hi Sagar,

    I assume most part of activity in your system would be on the company level? In such case I'd suggest you to add identity columns to the child tables and define clustered indexes as (Comp_NG, Id identity) there. There is also the possibility that Comp_NG as the left most column would benefit non clustered indexes if all your queries have Comp_NG as the predicate in where/join clauses.

    As you probably saw I mentioned clustered index, not primary key. Primary key is the logical design concept while clustered index is the physical design. In that solution you can define non clustered primary key either on ID or on other candidate keys in the system as long as those are unique. It's not the bad idea as long as you can live with extra index maintenance in case if you have the usage for this key/index there. In case if you decided not to add this index on ID column, again make sure that all queries have Comp_NG predicate in the query so you don't have table scans.

    Performance wise (Comp_NG, ID) index pattern introduces some extra index fragmentation in compare with clustered index on auto increment values but it would not be the huge one - data is evenly incrementing within the company scope. Same time it could improve some queries and bulk load because queries with comp_ng filter access data that logically located in the same place and bulk load imports the data to the different places in data files (no hot spots due new page/extent allocations with identity).


    Thank you!

    My blog: http://aboutsqlserver.com

    • Proposed as answer by Peja TaoEditor Wednesday, February 1, 2012 3:05 AM
    • Marked as answer by Peja TaoEditor Friday, February 3, 2012 7:46 AM
    Monday, January 30, 2012 2:22 PM
  • For me, the picture is too small to read.
    Monday, January 30, 2012 2:56 PM
    Answerer
  • Picture is not clear and also consider datatype column while taking screenshot.
    Wednesday, February 1, 2012 2:32 PM
  • Hi Sagar,

    There is no problems to add PK to all the child tables and put Clustered indexes for them these PK rather than the COMP_NO, this will be normal FK for the Parent table.

    Another Slution

    Also you could use the Generalization Concept by put all the tables that has the same columns together in one table and make a one-to-many relationship between the general tables and their child so you will have 3 levels of your relations

    COMP(COMP_NO[PK]) >> HEAD(HEAD_NO[PK], COMP_NO[FK]) >> ACCOHEAD (ACCHEAD_NO[PK], HEAD_NO[FK])

                                                                                   >> BLSHEAD(BLSHEAD_NO[PK], HEAD_NO[FK])

     

    Thanks,

    MOHAMED A. SAKR

    • Proposed as answer by MohamedSakr Thursday, February 2, 2012 3:29 PM
    • Marked as answer by Peja TaoEditor Friday, February 3, 2012 7:46 AM
    Thursday, February 2, 2012 3:29 PM