RE: database design conundrum
That would have been my suggested approach as well. I would suggest adding a sharewithall flag to tbl_shared_things so that the user has the option. I can be tedious to individually share information that you would gladly provide to anyone who asked for it, like office email, phone and fax. It also allows that the information could be easily displayed on an open profile if desired. Dave -Original Message- From: Michael van Leest [mailto:mvanle...@gmail.com] Sent: Thursday, March 20, 2014 10:30 PM To: cf-talk Subject: Re: database design conundrum That's basically it. Depending how your would query the data is entirely up on the specific use, so that is something fun to play with. This way you have all the flexibility you need, good luck! Michael 2014-03-21 2:38 GMT+01:00 PT cft...@gmail.com: so ... tbl_users (containing things that don't need to be shared) - userid displayName archived lastLoginDate lastLoginLocation joinDate ... tbl_things_that_can_be_shared - thingid thing (emailAddress, phoneNumber, birthday) tbl_shared_things (standard join table) - (pk) userid (fk to users.userid) (pk) thingid (fk to things.thingid) (pk) toShareWithUserID (fk to users.userid) (if they are in this table, then they are shared, so no boolean is needed) tbl_user_things - (pk) userid (fk to users.userid) (pk) thingid (fk to things.thingid) thingContents (the actual contents of the things for the user in question, like 'b...@example.com', '8885551212', '01/01/1980') Then make a view of the tbl_users-tbl_things_that_can_be_shared-tbl_user_things relationships to approximate a more traditional users table for easier querying. ? On 3/20/2014 8:56 PM, Michael van Leest wrote: I would build it like this: - tbl_users (userID etc) - tbl_user_things (thing id so you can add extra things without changing the DB) - tbl_user_join_thing (userID, thingID, setting (boolean 1/0) and a optional toShareWithUserID) Hope this helps ~| 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:358061 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: database design conundrum
Aye. Thought of that. There will be predefined groups, kind of like Facebook's Only Me, Friends, Friends of Friends, Everyone groups, but I wanted users to be able to exclude specific people from an entire group share in addition to adding individuals, should they desire. Hence the slightly overcomplicated design. Who knows? the feature might not ever get used or might end up being too resource intensive to keep, but better to build it into place now than try to cram it in later ... as long as it doesn't break anything. On 3/21/2014 10:02 AM, David Phelan wrote: That would have been my suggested approach as well. I would suggest adding a sharewithall flag to tbl_shared_things so that the user has the option. I can be tedious to individually share information that you would gladly provide to anyone who asked for it, like office email, phone and fax. It also allows that the information could be easily displayed on an open profile if desired. Dave ~| 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:358062 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: database design conundrum
third field, id of item to be shared. (id in this case being a unique column name)? On Thu, 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:358057 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: database design conundrum
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
Re: database design conundrum
I would build it like this: - tbl_users (userID etc) - tbl_user_things (thing id so you can add extra things without changing the DB) - tbl_user_join_thing (userID, thingID, setting (boolean 1/0) and a optional toShareWithUserID) Hope this helps 2014-03-21 1:46 GMT+01:00 Jerry Milo Johnson jmi...@gmail.com: third field, id of item to be shared. (id in this case being a unique column name)? On Thu, 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:358058 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: database design conundrum
so ... tbl_users (containing things that don't need to be shared) - userid displayName archived lastLoginDate lastLoginLocation joinDate ... tbl_things_that_can_be_shared - thingid thing (emailAddress, phoneNumber, birthday) tbl_shared_things (standard join table) - (pk) userid (fk to users.userid) (pk) thingid (fk to things.thingid) (pk) toShareWithUserID (fk to users.userid) (if they are in this table, then they are shared, so no boolean is needed) tbl_user_things - (pk) userid (fk to users.userid) (pk) thingid (fk to things.thingid) thingContents (the actual contents of the things for the user in question, like 'b...@example.com', '8885551212', '01/01/1980') Then make a view of the tbl_users-tbl_things_that_can_be_shared-tbl_user_things relationships to approximate a more traditional users table for easier querying. ? On 3/20/2014 8:56 PM, Michael van Leest wrote: I would build it like this: - tbl_users (userID etc) - tbl_user_things (thing id so you can add extra things without changing the DB) - tbl_user_join_thing (userID, thingID, setting (boolean 1/0) and a optional toShareWithUserID) Hope this helps ~| 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:358059 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: database design conundrum
That's basically it. Depending how your would query the data is entirely up on the specific use, so that is something fun to play with. This way you have all the flexibility you need, good luck! Michael 2014-03-21 2:38 GMT+01:00 PT cft...@gmail.com: so ... tbl_users (containing things that don't need to be shared) - userid displayName archived lastLoginDate lastLoginLocation joinDate ... tbl_things_that_can_be_shared - thingid thing (emailAddress, phoneNumber, birthday) tbl_shared_things (standard join table) - (pk) userid (fk to users.userid) (pk) thingid (fk to things.thingid) (pk) toShareWithUserID (fk to users.userid) (if they are in this table, then they are shared, so no boolean is needed) tbl_user_things - (pk) userid (fk to users.userid) (pk) thingid (fk to things.thingid) thingContents (the actual contents of the things for the user in question, like 'b...@example.com', '8885551212', '01/01/1980') Then make a view of the tbl_users-tbl_things_that_can_be_shared-tbl_user_things relationships to approximate a more traditional users table for easier querying. ? On 3/20/2014 8:56 PM, Michael van Leest wrote: I would build it like this: - tbl_users (userID etc) - tbl_user_things (thing id so you can add extra things without changing the DB) - tbl_user_join_thing (userID, thingID, setting (boolean 1/0) and a optional toShareWithUserID) Hope this helps ~| 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:358060 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
