Creating Groups: table and model

[eluser]Nouman6[/eluser]
Hey Guys,

So far I have it made so there is a signup area for a user, which they are given a unique ID, and a login form for them to load the members only area. Here they will view a list of groups they joined, or an option "Create Group" (which is where I am right now)

I can't wrap my head around what the best way to build this database structure would be.

groups:
- group_id
- group_name
- group_users?
- group_admin_id? (inserted when user creates the group)

within groups, there will be multiple users and options to add users (through email invite) and delete user.

also there will be a feature called 'tasks' which users can create 'tasks' and assign to other users.

and suggestions on a good, efficient way to build this?

thanks!

[eluser]Jondolar[/eluser]
You need a Groups table, a Users table and another table to join them UsersGroups. UsersGroups has group_id and user_id. This supports a 1 to many and a many to many relation. Now, keep everything about relationships out of the original Groups and Users table and relate them with UsersGroups. If want to track when they joined a group, add a field to UsersGroups. If you need a status of if they have been accepted in the group, add a field to UsersGroups (etc.)

Good luck with your project.

[eluser]Nouman6[/eluser]
thanks Jondolar, im very new to programming and the concept of database relations makes me shiver.

|---------|
|-Groups--|
|---------|
0 Group id
1 Group name

|---------|
|--Users--|
|---------|
0 user id
1 User name
2 User email
3 User pass

|-----------|
|UserGroups|
|-----------|
0 Group id (TAKEN FROM GROUPS (0)
1 user id (TAKEN FROM USERS (0)
2 Tasks?
3 comments? and so on

What exactly is usergroup going to hold? Is it just a bunch of rows of:

GROUP(1) USER(1) TASK(do this)
GROUP(1) USER(2) TASK(do that)
GROUP(2) USER(1) TASK(DO THIS)
GROUP(3) USER(1) TASK(do this)
GROUP(6) USER(8) TASK(DO THAT)

like that?

sorry for such basic questions, this kind of stuff is really what makes me get frustrated about programming

thanks!

[eluser]jedd[/eluser]
There's quite a few discussions around on plurals versus singular when it comes to naming your tables. I'm a strong advocate of singular, as plurals don't make sense to me, and they're much harder to keep consistent. Consistency is the thing you're really looking for here (and so I'd recommend you consider a singular approach first, and if it really annoys you, then once you've got your head around things, turn them back to plurals).

So ... what you've got so far is kind of sensible - until you get to the tasks stuff. You'll need to do the same thing with tasks that you've done with groups. That is - define what they are, what attributes they have, and how they will relate to other entities you have. An entity here is a user, a group, and a task.

You end up, basically, drawing an entity-relationship. And this is a surreptitious way of getting you into schema design.

Go read about normalisation on wikipedia. Find a book on the subject of database design - the basics are as old as the hills, so you can almost certainly borrow a good one from your library for free.

Code:

group
   id           (int, unsigned, auto-increment, etc)
   name    char ... whatever length

Code:

user
   id           (int, unsigned, auto-increment, etc)
   name    char ... whatever length
   email    char ... whatever length
   pass     whatever field you feel appropriate - char(32) is usually about right

Note that if you ever want to be able to record multiple email addresses for a given user, you'll (again) do the split here - you'll have an email table, and an email_user table. It's not scary - really it isn't. You just tend towards lots of very thin, very tall tables - as a general rule.

Code:

group_user    (most people adopt something like this - alphabetical sort so you don't have to think about it later)
   id              (int, unsigned, auto-increment, etc)
   group_id           (int, unsigned)
   user_id           (int, unsigned)
   comment     char ... whatever length

What exactly is usergroup going to hold? Is it just a bunch of rows of:

GROUP(1) USER(1) TASK(do this)
GROUP(1) USER(2) TASK(do that)
GROUP(2) USER(1) TASK(DO THIS)
GROUP(3) USER(1) TASK(do this)
GROUP(6) USER(8) TASK(DO THAT)

No - it will contain links (the user_id and group_id) to the user and group tables respectively.

Your SQL call will being in information from user, group and group_user tables in one query.

Tasks - if you tell us how they relate to a user and to a group, we can offer some more hints on that one.

sorry for such basic questions, this kind of stuff is really what makes me get frustrated about programming

All good in the hood. We've all gone through this process ourselves.

[eluser]Nouman6[/eluser]
[quote author="jedd" date="1267377330"]
Go read about normalisation on wikipedia. Find a book on the subject of database design - the basics are as old as the hills, so you can almost certainly borrow a good one from your library for free.
[/quote]

Will definitely have to read up on this!

[quote author="jedd" date="1267377330"]

Code:

group_user    (most people adopt something like this - alphabetical sort so you don't have to think about it later)
   id              (int, unsigned, auto-increment, etc)
   group_id           (int, unsigned)
   user_id           (int, unsigned)
   comment     char ... whatever length

[/quote]
I definitely agree with the naming


[quote author="jedd" date="1267377330"]
No - it will contain links (the user_id and group_id) to the user and group tables respectively.

Your SQL call will being in information from user, group and group_user tables in one query.
[/quote]

Still a little lost on this part.
After the suggestion of group_user, I thought it would be like this:
- User creates group
- group has individual properties, nothing related to the user.
- group_user is created, containing the user_id and the new group_id created

so for example when the user clicks the group name, to forward them to the group controller it would just check group_user to see if they have permission


[quote author="jedd" date="1267377330"]
Tasks - if you tell us how they relate to a user and to a group, we can offer some more hints on that one.
[/quote]

Tasks is a function of the group page. All users within that group can assign tasks to each other (and themselves) with assigned due dates and such. So they are unique to the group, and wen the user navigates to say another group they are joined in, they will see different tasks.

This is the same with the comments. Inside the 'dashboard' for the group, users will see a stream of update information. so when a user adds a task, its in the stream (in order) and above the stream is a box to update status, which is basically like adding a 'comment' into the stream. same with uploads, when a user uploads a file--sure it will be seen on the uploads page--however it will also display in the stream "john uploaded ____"

[quote author="jedd" date="1267377330"]
All good in the hood. We've all gone through this process ourselves.
[/quote]

thanks man! The tutorials everywhere mention the key factor of codeigniter being its forum and userbase, and I agree

[eluser]jedd[/eluser]
[quote author="Nouman6" date="1267403911"]
[quote author="jedd" date="1267377330"]
No - it will contain links (the user_id and group_id) to the user and group tables respectively.

Your SQL call will being in information from user, group and group_user tables in one query.
[/quote]

Still a little lost on this part.
After the suggestion of group_user, I thought it would be like this:
- User creates group
- group has individual properties, nothing related to the user.
- group_user is created, containing the user_id and the new group_id created

so for example when the user clicks the group name, to forward them to the group controller it would just check group_user to see if they have permission
[/quote]

Okay - you may need to be a bit clearer on which bit you're stumbling on.

The user can create a group - that's pretty straightforward, yeah? With auto-increment on ID, you just need to get the details for the new group, probably assume the user will be a member of it, and create a row in group and a row in group_user.

What kind of properties are you talking about when you say that a group has individual properties? Just name and comments, that kind of thing?

Permissions are easy - as you observe, you check if the user & the group ID's exist in the same row of group_user.

All users within that group can assign tasks to each other (and themselves) with assigned due dates and such. So they are unique to the group, and wen the user navigates to say another group they are joined in, they will see different tasks.

You need to be really clear here. Is a task attached to a user, or a group, or a specific combination of both?

What kind of details do you need to record for a task? It sounds like (!) but correct me if I'm wrong:

Code:

task
   id           (int, unsigned, auto-increment, etc)
   group_id           (int, unsigned, FK to group.id)
   user_id           (int, unsigned, FK to user.id)
   created_by    (int, unsigned, FK to user.id)
   description     char ... whatever length
   created_on   (date / time field)
   due_on    (date / time field)

This assumes a task is specific to a particular user/group combination.

This is the same with the comments. Inside the 'dashboard' for the group, users will see a stream of update information. so when a user adds a task, its in the stream (in order) and above the stream is a box to update status, which is basically like adding a 'comment' into the stream. same with uploads, when a user uploads a file--sure it will be seen on the uploads page--however it will also display in the stream "john uploaded ____"

Perhaps a smaller project to start with ...

Nah, I'm sure we can do this. Let's get the task stuff sorted first, though, and see how we go from there.

[eluser]Nouman6[/eluser]

Code:

Okay - you may need to be a bit clearer on which bit you’re stumbling on.

The user can create a group - that’s pretty straightforward, yeah?  With auto-increment on ID, you just need to get the details for the new group, probably assume the user will be a member of it, and create a row in group and a row in group_user.

What kind of properties are you talking about when you say that a group has individual properties?  Just name and comments, that kind of thing?

Permissions are easy - as you observe, you check if the user & the group ID’s exist in the same row of group_user.

Ya the individual properties from what I understand will just be the group ID auto-increment, and the group name. Things unique to a group are of course it's comments, and tasks.

You need to be really clear here. Is a task attached to a user, or a group, or a specific combination of both?

What kind of details do you need to record for a task? It sounds like (!) but correct me if I’m wrong:

Code:

task
   id           (int, unsigned, auto-increment, etc)
   group_id           (int, unsigned, FK to group.id)
   user_id           (int, unsigned, FK to user.id)
   created_by    (int, unsigned, FK to user.id)
   description     char ... whatever length
   created_on   (date / time field)
   due_on    (date / time field)

This assumes a task is specific to a particular user/group combination.

I believe you nailed it on the head. The task should be for that group, for a user in that group. From what I can see, that table setup is perfect. Where it shows who created that task, who it is assigned to, the description of it, created date, and due date. which is exactly what I imagined!

Perhaps a smaller project to start with ...

Nah, I’m sure we can do this. Let’s get the task stuff sorted first, though, and see how we go from there.

haha I know! I thought this was the simplest out of my application ideas so I went ahead with it, and now I notice how complicated things can get. I did manage to get the registration/user login items created http://work.grp.ly/ But hey of course that's just a single database

the current progress of that is they are already to the user dashboard area, ready to click 'create group' and enter in a group name.

I was able to borrow a database book, so I'll try my best to read on this

and again, thanks so much man, for both taking the time to understand what I'm trying to imply and respond with such great detail!

[eluser]Nouman6[/eluser]
Okay so making some progress

I've got the users mad (like I mentioned) and now I have the user able to create a group, so I'm wondering when I should/how I show create group_user

Code:

<?php

class Group_model extends Model {
    
    function create_group(){
        $new_group_data = array(
            'group_name' => $this->input->post('group_name'),
            );
        $insert = $this->db->insert('group', $new_group_data);
        $group_user_id = $this->db->insert_id();
        echo $group_user_id;
    }

}

it calls function create_group once the form was checked and everything, and right now I don't have it returning to the controller just yet since i THINK this is the time to create the group_user table, right?

I have the user data stored in session (id) and I am getting the newly created group id in the last two lines of that function, and it is echo'ing correctly.

so is this where I create group_user? and then return to the controller and tell the user SUCCESS YOUR GROUP WAS MADE?

thanks!

[eluser]jedd[/eluser]
[quote author="Nouman6" date="1267524626"]

Code:

class Group_model extends Model {
    function create_group(){
        $new_group_data = array(
            'group_name' => $this->input->post('group_name'),
            );
        $insert = $this->db->insert('group', $new_group_data);
        $group_user_id = $this->db->insert_id();
        echo $group_user_id;
    }

[/quote]

We tend to not do this. I don't mean the echo in your model - that's obviously debugging stuff. But rather the model taking stuff direct from $post data. Partly for security reason, but mostly because it muddies the MVC model.

What I would do is have a function that took the necessary parameters:

Code:

// somewhere in my model ...
function create_group ( $groupname = NULL )  {
   // .. your DB stuff - check if groupname already exists, insert if it doesn't, etc

And, of course, in your controller you extract & validate your $post data, and then pass it over as a parameter.

Now, you also have a design decision to make somewhere here - whether to have one model for every table, or to have some models that 'own' several tables. My preference is the latter. A CRUD approach leads you towards the former. Multiple tables to a model makes it (arguably) easier to do multi-table updates. Hybrid approaches exist (eg. a model can read from any table, but each table has just one, clearly defined, model that writes to it). Et cetera.

To answer your last question - yes, this is where you do the create, and return a boolean to identify if the create_group actually worked, back to your controller.

[eluser]Nouman6[/eluser]

// .. your DB stuff - check if groupname already exists, insert if it doesn't, etc

hmm I was under the impression that checking for a unique entry such as the name was done by the controller during form_validation through callback. Here's how I have it setup.

Code:

function validate_group(){
        $this->load->library('form_validation');
        $this->form_validation->set_rules('group_name', 'Group Name', 'trim|required|min_length[3]|callback_groupname_check');

        if ($this->form_validation->run() == TRUE){ //If the user filled out all fields, pass it to model
            $this->load->model('group_model');
            if($query = $this->group_model->create_group()){ //pass model
                $data['return_reason'] = 'Signup Succesful!';
            }else{ // passed form, failed model
                $this->new_group();            
            }
        } else { // User failed input form, send them back with errors
            $this->new_group();
        }

    }
    function groupname_check($str){
        $this->db->where('group_name', $str);
        $query = $this->db->get('group');
        if ($query->num_rows() > 0){ //if there is another group with the same name
            $this->form_validation->set_message('groupname_check', 'This %s is taken');
            return FALSE;
        }else{ //if groupname is unique
            return TRUE;
        }
    }

BUT if this is the wrong way, I'd love to know

Now, you also have a design decision to make somewhere here - whether to have one model for every table, or to have some models that ‘own’ several tables. My preference is the latter. A CRUD approach leads you towards the former. Multiple tables to a model makes it (arguably) easier to do multi-table updates. Hybrid approaches exist (eg. a model can read from any table, but each table has just one, clearly defined, model that writes to it). Et cetera.

My idea was to have say:

member_model. this controls everything about the member from create member, to validating the login.

group_model this controls group creation as well as group_users, AND to have it manage the items inside of the group like the functions of a group. adding comments, uploading files, assigning tasks.

though If that's a bad way, I'd love to learn the proper way!