A Mysql Question.....

[eluser]flyenig[/eluser]
PLEASE LOOK AT MY SECOND POST FOR A BETTER UNDERSTANDING OF THIS TOPIC

Ok i have a users system. When some action happens to a user, it will email them.

For example,
If john friend requests Kenny then he will get an email saying "John requested you blah blah blah..."
if meg comments on kenny's photo then it will email him saying "Meg commented on your photo blah blah blah..." and so on and so on...

I have 10 actions so far and as my site grows, I plan on having more.

the problem is im trying to see how all that would work on a mysql table.
I was thinking of putting 10 fields in my "users" table eg.

Code:

ID - firstname - lastname - (15 other fields eg email - birthday...) notification_1 - notification_2 - so on...

I have no problem with this except my site will grow and i think that too much fields is bad. Am I right?

OR

should i create a new table named "users_settings"
and have

Code:

ID(the users id) - notification_1 - notification_2 - notification_3 - notification_4- so on...

what is a good way of accomplishing this? or is one of the methods I posted the best way? Help me out? Thanks

[eluser]mddd[/eluser]
Why would you save all of that to a database when all that changes is the name?
In my opinion, sending emails is best done using views. Create a view for every email. You can make a folder 'emails' inside the views folder. And then you can load a view just like any other, for instance

Code:

$email_body = $this->load->view('emails/comment', $data, true);

In $data you put the information you need for the view, just like with any other view.
Most of the time, you already know this information, because your are saving the comment, birthday or whatever just before you send the email.

You catch the output of the view (in my example: in $email_body). And then send it using the Email class. Something like:

Code:

$data = $this->comment_model->get_comment($id);
$this->email->to($data['to_email']);
$this->email->from($data['from_email']);
$this->email->message($email_body);
$this->email->send();

Of course you can expand on this. But I hope it shows the basics.

[eluser]flyenig[/eluser]
Thanks for the reply but you are talking about something else. Sorry, i failed to explain more about what im trying to do.

Have you ever went on facebook and went to email notification settings or have u ever went on twitter and went to notifications settings? if you didnt, they have some settings like,

"Email me when....

someone sends me a message [checkbox here]
Somone sends me a photo comment [checkbox here]
Someone does something [checkbox here]
Someone Does another thing [checkbox here]

and if they click the check box, each checkbox value is saved in a table that has

Code:

ID(the users id) - notification_1 - notification_2 - notification_3 - notification_4- so on...

and notifcation_1 .. 2.. 3 are Booleans. I think i explained it well now lol

and my email templates are stored in views.

When someone sends kenny a message, it will search in a table for the field "notification_1", and if it has a value of "1", it will email kenny, if it has a "0" then it will not email him. Would I store all these fields in the users table which already has a lot of fields? Or should I make a new Table named, "users_settings" or something?

[eluser]mddd[/eluser]
Ok, now you are making sense. From your original post, it was not clear to me that you meant storing the PREFERENCE in the database.

I think adding all of the fields to the users table is not a good idea. That way, you get loads of fields. The option of making a separate table is okay, but it has the disadvantage of growing every time you add a setting in the future.

You might consider making a table that is like this:

Code:

id        user_id   setting   value

Every user/setting combination has its own row in the table. That way you can add settings when you want, you'll never have to change the table design!

[eluser]flyenig[/eluser]
Yea sorry about not explaining lol :-P

[quote author="mddd" date="1276195695"]
You might consider making a table that is like this:

Code:

id        user_id   setting   value

Every user/setting combination has its own row in the table. That way you can add settings when you want, you'll never have to change the table design![/quote]

Ok so you are saying I should have a separate table that has

Code:

id - user_id - notification_1 - notification_2 .... notification_10

like that?

[eluser]mddd[/eluser]
No, that's not what I meant. I meant a table like this:

Code:

id   user_id   setting   value
1    1         comment   1
2    1         birthday  0
3    1         photo     0
...
...

So in this example, user #1 wants to share comments, but not birthdays or photos.
The advantage of this idea is that if you want to add another setting, like 'music' or whatever, you don't need to change the table. Just insert a new row for each user that wants to share music. And you're done.

You can even choose to leave out the 'value' column and only store settings for the preference that people have checked:

Code:

id   user_id   setting
1    1         comment
2    1         birthday
3    2         comment
4    3         ...
...

In this example, user 1 is sharing comments and birthdays, user 2 is sharing only comments.
A disadvantage of this, is that you can only store yes or no-settings. In the previous example, the value field could also hold other values, for instance a setting for the number of updates that they want to receive per week, or whatever.

[eluser]flyenig[/eluser]
ohh ok i see what you are saying. Lets just say I have 100,000 users on my site, wouldnt that kinda slow down the my CI site when more users make accounts?

[eluser]mddd[/eluser]
Not really. As long as you put the right indexes on your tables it will be fine.
Of course you could also use numbers for the settings (like comment=1, birthday=2, etc) if you like.
Or use a ENUM type field to hold the values.
There are many variations but I think the general idea is clear and works very well.

[eluser]flyenig[/eluser]
Thank you, for the reply.

I have another question, is their any thing wrong with doing it this way?

Code:

user_id    not_1    not_2   not_3   ....
23           1        0       0     ....
1            0        1       0     ....
10           1        1       0     ....
56           1        1       1     ....
......

and a row will only be created if someone edits there notification settings.

[eluser]garymardell[/eluser]
You could actually just have a single field in your users table for preferences and use a serialized array to store the options.

So you have an array like

serialize(array("birthday" => true, "comment" => false))

And store that. So the script can just take the serialized array, unserialize it and then get the options and then if the options update just serialize the array again and store it back.

I don't know if this method is recommended but would certainly work and be fairly easy.