locked
Data Separated a comma in the same column ?? RRS feed

  • Question

  • Hi everyone , 

    in fact i have a question i have a table with administrations names , and so far it was enough , but recently i was asked to add user authentification to my application (my work is Done by DataZen publisher ) , so in order to do that , i'm adding a colum to my table with usernames , but since i have really a lot of users, and my issues is specially that : <o:p></o:p>

    one user might be addedd in many rows , since he might be related to many administrations . <o:p></o:p>

    and the same administration might have many users with the same rights , so is it possible in the same column  for exemple to write user separated by a comma ? ( i have really a lot of users and so many rights , that we're still figuring   out ) <o:p></o:p>

    example : 

    Administration       ||         Usernames 

    ----------------------------------------------

    Administration A    ||     UserA, UserB, UserC

    Tuesday, February 13, 2018 2:54 PM

Answers

  • its possible but not recommended as it in violation of First Normal Form. Storing data like this makes it hard to modify data or to search for a particular user etc. Hence way to store it is by means of a bridge table which joins user to administration by means of one to many form.

    so model will look like this

    UserAdmin

    -------------

    AdminUSerID
    AdminID - FK to Admin table
    UserID - FK to user table

    and you will store like this data

    Administration
    ---------------------
    
    AdminID   AdminName
    ------------------
    
    101    Administration1
    102    Administration2
    
    User
    ----------------------
    
    
    UserID UserName
    -----------------------
    1001 User1
    1002 User2
    1003 User3
    1005 User4
    1006 User5
    ...
    
    
    
    UserAdmin
    -----------------
    
    AdminUSerID    AdminID     UserID
    ---------------------------------------
    100001         101      1001
    100002         101      1002
    100003         101      1003
    100005         102      1005
    100006         102      1006
    100008         106      1007
    ...


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page


    • Edited by Visakh16MVP Tuesday, February 13, 2018 3:21 PM
    • Marked as answer by RaniaD Wednesday, February 14, 2018 1:16 PM
    Tuesday, February 13, 2018 3:16 PM

All replies

  • its possible but not recommended as it in violation of First Normal Form. Storing data like this makes it hard to modify data or to search for a particular user etc. Hence way to store it is by means of a bridge table which joins user to administration by means of one to many form.

    so model will look like this

    UserAdmin

    -------------

    AdminUSerID
    AdminID - FK to Admin table
    UserID - FK to user table

    and you will store like this data

    Administration
    ---------------------
    
    AdminID   AdminName
    ------------------
    
    101    Administration1
    102    Administration2
    
    User
    ----------------------
    
    
    UserID UserName
    -----------------------
    1001 User1
    1002 User2
    1003 User3
    1005 User4
    1006 User5
    ...
    
    
    
    UserAdmin
    -----------------
    
    AdminUSerID    AdminID     UserID
    ---------------------------------------
    100001         101      1001
    100002         101      1002
    100003         101      1003
    100005         102      1005
    100006         102      1006
    100008         106      1007
    ...


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page


    • Edited by Visakh16MVP Tuesday, February 13, 2018 3:21 PM
    • Marked as answer by RaniaD Wednesday, February 14, 2018 1:16 PM
    Tuesday, February 13, 2018 3:16 PM
  • thank you so much visakh16 ,

     i was like hesitating to use a bridge table , felt like it wouldn't change much , but you're right about the search part  or the update part.

    thank you again 

    Wednesday, February 14, 2018 1:18 PM
  • thank you so much visakh16 ,

     i was like hesitating to use a bridge table , felt like it wouldn't change much , but you're right about the search part  or the update part.

    thank you again 

    you're welcome :)

    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Wednesday, February 14, 2018 1:22 PM