[SOLVED] PHP User Alerts (Reminders)

[jutaro]

So, I have made a package that handles all of our prospects, clients, and project management for the most part and I am slowly adding more and more features, but just now I hit a wall and I am looking for input.

 

The system has multiple users (The Staff), each which has its own Calendar and Alerts(reminders, automated alerts from the system).

 

I have a users table, and I have an alerts table.

 

My question is this:

 

What is the best way to handle this data if the alerts are capable of being assigned to MULTIPLE users? Currently my table is setup to take the UserID and AlertID. Obviously, that is best used for just one user.

 

Here are a few scenarios that I am familiar with....

 

1.) Simply just duplicate the alert for each user it's assigned to. Duplicate data sucks though.

2.) Create another table for alerts_to_users which will just hold the branching data to bring the two together. But again, this will have many many records for just one alert (assigned to multiple people)

3.) Change the UserID field on the alerts to a text field and just store each UserID separated by a delimiter which I can later parse.

 

Does anyone else know a better way to handle this? I want to avoid duplicate data. I don't want to have complex queries that slows the entire system down. And #3 would require a lot of processing of data which could slow it down quite a bit.

 

Thanks for your help in advance

[revraz]

#2 I think would be the most efficient and would probably be how I would do it.

[jutaro]

Thanks! That's what I was thinking. It will involve a lot more work in the back-end though.. For my particular uses anyways. But it will be worth it!

[revraz]

Not really, it's just a table that holds the UID and the AlertID, and a simple JOIN to get the data out of it.

 

That's how I relate my Reservation DB.

[jutaro]

Well I was referring to how I was going to handle adding and editing the alerts and stuff with my original plan. I will just have to change that around a bit.

 

At first I was going to just have a select box where you could hold CTRL and select all the users attending, but with this method it would require a lot of checks when/if you edit an alert.

 

You create an alert with users 1, 3, 5.

It stores alert

It stores alert to users for each user, 1, 3, 5

 

You edit Alert and remove user 1 and add user 2

With the original way of using a select box for the users I would have to check for existing records for all users for that alert, delete 1 in this case and then add 2.

 

But I will just make the Users an additional part of the alerts where you can add multiple users that way, but when you edit it if you want to delete a user you just click delete next to that user.

 

I could be over-thinking this a lot though. I'll figure it out.

 

Thanks :-) .