Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
how I can have a common column to many table in sql server 2008

Answered how I can have a common column to many table in sql server 2008

  • Sunday, June 03, 2012 12:55 PM
     
     
    Sign In to Vote
    0

    Hi all,

    I have many Tables with different attributes .in sql server 2008 . but all Tables primary key is UNITSLNO. and I am planning to give different user different permission on these tables . ie some user will have viewing power some have editing power etc. 

    more clearly the entire table will be UNITSLNO  column1    column2    column3    column4   column5    ......... columnP

    but I am dividing the tables like this 

    table1

    UNITSLNO  column1  column2  column 3

    table2

    UNITSLNO   column 4  column 5   column6  

    table3

    UNITSLNO   column 7  column 8   column9  and so on .... 

    the set of UNITSLNO I can say is global . table1 is owned by user1, table 2 is owned by user2 table3 is owned by user4 in a multy user environment with different priority for different user. for example UNITSLNO starts from 100 to 998.

    user1 added a new entry UNITSLNO=999. it should be replicated or intimated to all other users or table so that each table will have now UNITSLNO from 100 to 999.

    another case 

    user2 deleted UNITSLNO 10  from table2 because for him he thought the data is irrelevant. but it should not be deleted from any  other user /table at most they may be intimated that user2 deleted UNITSLNO= 10 from his table .

    in essence addition should be propagated across deletion should not(deletion also should be propagated but it shoukd ask other user permission for confirming the deletion from his table) 

    how I can deal such a situation  ; What sort of KEY should UNITSLNO be 

    Regards and thanks in advance 

    Iqbal

    itismeiqbal

     

All Replies

  • Sunday, June 03, 2012 12:59 PM
    Answerer
     
     Answered
    Sign In to Vote
    0
    Probably having a VIEW with WHERE condition for different users and GRANT SELECT permission on those views not on underlying tables 

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

    • Marked As Answer by iqbal1980 Saturday, June 09, 2012 1:23 PM
    •  
     
  • Sunday, June 03, 2012 1:13 PM
     
     
    Sign In to Vote
    0

    Regarding the user features I am using user name and password for different users. I am worried about the UNITSLNO synchronization among different table

    Regards

    Iqbal

    itismeiqbal

     
  • Sunday, June 03, 2012 2:45 PM
    Answerer
     
     
    Sign In to Vote
    0
    Looks too complicated issue. If I understood correctly, user1 deletes data from t1 and it should be propagated to other tables where   this user does not have permission for DELETE, am I right?

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

     
  • Sunday, June 03, 2012 4:10 PM
     
     
    Sign In to Vote
    0

     more clearly if I say

    UNITSLNO is the primary key shared by all users . if a particular user delete one entry for example UNITSLNO 10 , from his table , it will be disappeared from his table. but at the same time the entries corresponds to UNITSLNO 10 , in other tables of other users cant be deleted . but these users can be intimated by some message that user1 deleted UNITSLNO10 , so it is up to other users choice to delete or not.

    the philosophy is so simple, the column UNITSLNO should be common to all . if some user add one entry all other shall be informed about it .

    Regards

    Iqbal

    itismeiqbal

     
  • Monday, June 04, 2012 5:48 PM
     
     
    Sign In to Vote
    0
    what is abusive in this ???

    itismeiqbal

     
  • Tuesday, June 05, 2012 4:01 PM
     
     
    Sign In to Vote
    0

    Hi friends

    somebody reported it as abusive why I do not know 

    Regards

    Iqbal

    itismeiqbal

     
  • Tuesday, June 05, 2012 4:46 PM
     
     
    Sign In to Vote
    0
    Someone was probabally just suggesting moving your question to the correct forum - I wouldn't worry about it.

    Chuck Pedretti | Magenic – North Region | magenic.com