Multiple users RRS feed

  • Question

  • Hello everyone,

    What if I have an addressbook application and more users want to use it. So I log in and I see my addressbook contacts and when my sister logs in then she sees her addressbook contacts. How do I design a database that has multiple addressbooks within them? Do I create for every user a total set of tables? And what if I dont know the amount or which users in front. Should I create within every table a userID so that I know to which person belongs the record?

    Any tips or advice on this.



    Tuesday, July 17, 2012 4:17 PM


All replies

  • Hi

    Actually it is best to maint it through Application`s user creation and assigning roles privilage .

    In ADDRESS tables of your database make an extra column AddressCreatedBy and action Date.

    Whenever any one log on to your application, then he will only view those addresses which that user has created earlier.

    SQL will be as below:

    Select * frrom ADDRESS where AddressCreatedBy =`Spacelama` 

    --log in user is :Spacelama and then he will view only thoes addresses created by Spacelama

    For any query replay...........


    Ahsan Kabir

    Tuesday, July 17, 2012 4:35 PM
  • It really depends on whether you want complete seperation or if you want to share some data. If your user count is small and you want complete seperation, multiple completely seperate databases could be easier and safer. If you really want a shared experience, then you will need to do some row level security like I described in a different email.


    Without good requirements, my advice is only guesses. Please don't hold it against me if my answer answers my interpretation of your questions.

    Tuesday, July 17, 2012 7:08 PM
  • They dont need to use eighother addressbook. The addressbook is private and can only be shared if the owner allows the other user to share some selected contacts. The best practice for that I dont know. XML is one option...

    Let's say I create this application for my whole family. And when they use it they have there own addressbook.

    A multiple complete seperate database is that designable within the database? I mean without coding a whole table set for a new user? And if this is possible how should i design this? And if it aint possible I should add within every table the user name? Or put all the tables within a folder with the userId?

    I guess it isnt logic to insert a userId to every table...



    Tuesday, July 17, 2012 7:26 PM
  • Take a look at this approach. Basically you need to create a view (WHERE condition inside) to show only data allowed  to the specific user that logged in. http://vyaskn.tripod.com/row_level_security_in_sql_server_databases.htm

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

    Wednesday, July 18, 2012 6:59 AM