How to design Multi user sharing data/records

• raju.tanneeru

  Right there with Babe

  Points: 763

  More actions

  September 15, 2009 at 9:37 am

  #156218

  Hi All,

  i have a scenario where need to handle the same sharing data for multiple users.

  the scenario is something like this.

  in my application(asp.net 1.1) , i am having a dashboard. when a user logins into the application he will have a list of notifications sent by Management/heigher level users. so the same record can be send to more than one user. Any user can delete that particular notification. Once it is deleted then should not show next time for that user.

  right now for each notification record in tr_notifications table i am having a column assigned user, where i am adding all assigned user by comma(,) separated.

  that problem is each user may having notifications in 100's. in my DB this notifications data is occupying 40% of size. as the size is huge it is getting slow when loading the data to UI

  can any one help me out how to better handle this.

  in similar way other 50% data base is for attachments.

  we are planning to migrate to SQL 2008, can any one please advise on the above two issues to handle better

  Thanks in advance

• Steve Jones - SSC Editor

  SSC Guru

  Points: 728159

  More actions

  September 15, 2009 at 10:14 am

  #1053938

  Don't stick multiple users in the same column. You're slowing down access.

  Instead you'd typically have another table that links the user with the notification. As in

  create table notification( noteID int identity(1,1), detail varchar(200))

  go

  create table Usernotification( UserID int, NoteID int)

  go

  when a user needs his notifications

  select n.detail

  from Notification n

  inner join usernotification u

  on n.noteid = u.noteid

  where u.userid = @userid

  Follow me on Twitter: http://www.twitter.com/way0utwest
  Forum Etiquette: How to post data/code on a forum to get the best help
  My Blog: www.voiceofthedba.com