Actually I didn't really mean ACL, just Users and Groups. You could
have a Group identified by the name "Friends", that was owned by the
User and had members that were the friends. Then you'd have another
Group identified by the name "Invitees" with the same sort of setup.
I have something like this:
-- --------------------------------------------------------
--
-- Table structure for table `users`
--
DROP TABLE IF EXISTS `users`;
CREATE TABLE IF NOT EXISTS `users` (
`user_id` bigint(20) NOT NULL auto_increment,
`user_name` varchar(30) collate utf8_bin NOT NULL,
`full_name` varchar(100) collate utf8_bin default NULL,
`user_email` varchar(50) collate utf8_bin default NULL,
`user_password` varchar(255) collate utf8_bin default NULL,
`status_code` varchar(15) collate utf8_bin NOT NULL default 'New',
`date_created` timestamp NULL default NULL,
`user_created` varchar(30) collate utf8_bin NOT NULL,
`date_modified` timestamp NOT NULL default CURRENT_TIMESTAMP on
update CURRENT_TIMESTAMP,
`user_modified` varchar(30) collate utf8_bin NOT NULL,
`group_id` bigint(20) default NULL COMMENT 'Primary group of user',
PRIMARY KEY (`user_id`),
UNIQUE KEY `user_uk1` (`user_name`,`status_code`),
KEY `fk_users_group` (`group_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
AUTO_INCREMENT=6 ;
-- --------------------------------------------------------
--
-- Table structure for table `groups`
--
DROP TABLE IF EXISTS `groups`;
CREATE TABLE IF NOT EXISTS `groups` (
`group_id` bigint(20) NOT NULL auto_increment,
`group_name` varchar(100) collate utf8_bin NOT NULL,
`date_created` datetime default NULL,
`user_created` varchar(30) collate utf8_bin default NULL,
`date_modified` datetime default NULL,
`user_modified` varchar(30) collate utf8_bin default NULL,
`user_id` bigint(20) default NULL COMMENT 'User that owns the
group',
PRIMARY KEY (`group_id`),
KEY `fk_groups_user` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
AUTO_INCREMENT=3 ;
-- --------------------------------------------------------
--
-- Table structure for table `user_groups`
--
DROP TABLE IF EXISTS `user_groups`;
CREATE TABLE IF NOT EXISTS `user_groups` (
`user_group_id` bigint(20) NOT NULL auto_increment,
`user_id` bigint(20) NOT NULL,
`group_id` bigint(20) default NULL,
`status_code` varchar(15) collate utf8_bin NOT NULL default 'New',
`date_created` timestamp NULL default NULL,
`user_created` varchar(30) collate utf8_bin NOT NULL,
`date_modified` timestamp NOT NULL default CURRENT_TIMESTAMP on
update CURRENT_TIMESTAMP,
`user_modified` varchar(30) collate utf8_bin NOT NULL,
PRIMARY KEY USING BTREE (`user_group_id`),
KEY `fk_user_jobs_groups` (`group_id`),
KEY `fk_user_jobs_users` (`user_id`),
KEY `fk_user_groups_groups` (`group_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
AUTO_INCREMENT=5 ;
Just create a row in the Groups for "Friends" and "Invitees" for each
new user, and then you'd need to build the "add friends" UI bits to
add them to the 'user_group' join table. This would be a little more
OO-like, since you could easily extend the sorts of groups to be other
things ("People I hate", "Fans", etc.) without adding more tables to
the mix.
For the simplest and cleanest approach though, I think you are on the
right track, the user_friends table could indeed just be the 'self'
join between users. I'd probably make the join table have three
columns: id, user_id and friend_user_id
On Dec 1, 10:24 pm, boomswitch <[EMAIL PROTECTED]> wrote:
> Hi,
>
> Thanks for the response.
>
> So to clarify your first idea -- would my model associations look
> something like this?
>
> - Invitations belongTo Users (senders/creators).
> - Invitations haveAndBelongToMany Users (invitees)
> - Friends belongTo Users
>
> One issue I'm seeing with all this is invitees are often not
> registered users, but just a bunch of email addresses. Once they get
> the invite, they have to register, but does that change the
> associations or models much? I am thinking I could have an "active"
> field in my Users model/table that puts all fields to null except
> their email address, then when they hit the invite link and register
> their username/password, they become "active".
>
> I think this will all work right so long as the Friends belongTo Users
> association can be pulled off. I have a table right now that is
> similar to what you described, but it's like this
>
> user_id, varchar(255)
> friend_id, varchar(255)
>
> so if user 'john_doe' has user_id in the Users table of 1, and he has
> 3 friends with different user_ids, the table users_friends would look
> like:
>
> user_id | friend_id
> ==============
> 1 | 7
> 1 | 12
> 1 | 19
>
> and so on.
>
> Your ACL Groups idea is interesting. Any suggested reading for that
> 'generic' setup? Sounds more simplified, but I'm not sure how the
> database tables and model associations would compare to the first idea
> I've outlined.
>
> Thanks again
>
> On Dec 1, 1:04 pm, Rob <[EMAIL PROTECTED]> wrote:
>
> > In a way, you're building groups similar to what you'd do for ACL.
>
> > Based on what you've outlined, I would just have the Invitations
> > belong to a User (sender), and HABTM Users (invitees), and then have
> > another relationship for "Friends" that would belong to a User, and
> > contain the Users that are "Friends".
>
> > That said, you could also go for a more generic approach like is used
> > for ACL, and have Users and Groups, where there is a HABTM between
> > Users and Groups, and then have one Group that would be "Friends" of a
> > User, and another group that would be the "Invitees" for an event.
>
> > You need the join table if the relationship is going to be many-to-
> > many, you can accomplish the same thing without the join table if you
> > denormalize the keys back into the other table (which is typically
> > what you want for one-to-many or one-to-one sorts of relationships).
>
> > For example, you could have your "Friends" table include a foreign-key
> > for owner_user_id (owner) and another for user_id (friends), which
> > would result in one row in the "Friends" table for each User that is a
> > "Friend". Really depends on the volume of data and how normalized you
> > want to make your data model IMHO.
>
> > On Nov 30, 5:24 pm, boomswitch <[EMAIL PROTECTED]> wrote:
>
> > > Hi all,
>
> > > New here, new to CakePHP, but loving it so far.
>
> > > I'm working on planning a basic invitation application, but I'm
> > > struggling with how to plan my database and some model associations.
>
> > > A few things about my current setup:
>
> > > - CakePHP 1.2-latest, Apache, MySQL
> > > - I have a working User (authentication) model
>
> > > For this invitation system, there are a few seemingly simple
> > > requirements that I'm struggling to design properly within Cake:
>
> > > - Users can invite Friends to events.
> > > - Users can invite Users to events.
> > > - Friends are registered users that Users have added to their Friends
> > > list.
>
> > > In other words, all persons invited to an event are registered users.
> > > However, some are marked Friends and some aren't. How can I relate
> > > this all properly to the User model? I think I have a few of these
> > > right, but I'm wondering if there's a better way to keep Friends Lists
> > > and also associate both Friends and non-Friends (Users) to
> > > Invitations.
>
> > > - Invitations "belongTo" a User (the creator/owner).
> > > - User "hasMany" Invitations.
> > > - Invitations have many invitees which can be Friends or non-Friends
> > > -- all of which are Users.
>
> > > How do I store my list of all the invitees for an Invitation? Would I
> > > need an Aquaintances model (for non-Friends that are invited) with an
> > > hasAndBelongsToMany association and use a join table in MySQL? Or
> > > would there be a simpler design...
>
> > > Currently, I've got a working Friends List by using a join table that
> > > stores user_id and friend_id.
>
> > > All feedback is greatly appreciated, be gentle, I'm new to PHP and
> > > specifically CakePHP. :)
>
> > > Thanks!
>
>
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"CakePHP" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/cake-php?hl=en
-~----------~----~----~----~------~----~------~--~---
