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
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 :
>
> 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
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 :
>
> 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
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
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 : > > 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 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
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 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 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
