For clarification, are the values for the things going to be in the users table (i.e. - contact info) or does each thing have its own table (i.e. - an item of ownership). My preferred solution, if its the latter would be
Tables (note: names for clarity, not convention): users - user_id,username,password, email, etc... thing1 - thing_id,owner_id,thing_info users2thing1 - key,thing_id,user_id If thing can only be owned by one user, then the user who controls the thing is hard-coded in to the CRUD methods of the thing. If the things are part of the user table, it gets messier, but the easiest way Ive found is to encapsulate all of the relationships for the things in one table and use bit/boolean values to flip them on or off. Even if you have a lot of values your queries will be fast because youre dealing with indexed keys and simple values only. users - user, thing1, thing2... user_things - key,user_id,relation_user_id,thing1(bit),thing2(bit)... Thats how I have and would approach it but now Im also curious as to how others would. HTH, Jon On Mar 20, 2014, at 8:33 PM, PT <cft...@gmail.com> wrote: > > I have a design issue I can't seem to get my head around. > > Say I have a table full of users with certain fields containing > information they can share with or hide from other users. > > users_table > ---------- > userid > thing_to_share_or_not_1 > thing_to_share_or_not_2 > thing_to_share_or_not_3 > ... > > There are a lot of "things" > > The default is to hide. What is the most efficient way to denote that a > user is sharing specific info with another user? > > I immediately thought about a joining table > > sharing_table > ---------- > userid_of_sharer > userid_of_user_being_shared_with > > I can't figure out how to join what specific piece of information is > being shared. > > I thought about including a varchar in the share table with the name of > the shared field in it, but, while doable, seems a clumsy hack. > > What obvious thing am I overlooking? > > > Todd Ashworth > Janty Networks > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:358056 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm