I am making a web-based application written in PHP and MYSQL (My first full app, hooray!).
I won't go into details much, but here is a dilemma I have:
The application will have events, and users who didn't read the event content yet should see the event as "NEW".
Now, the question is how to implement it.
I have a users and Events tables in my database.
Adding all the IDs of user who did see the event to the Events DB seems a bad idea, the DB will grow very quickly.
Reversing the process and deleting IDs of users who saw the event seems better, but the Event row in the DB increases size and that impacts.
So I thought of adding each "NEW" Event ID to every user who didn't see it. Seems more logical to me. When the event is no longer "NEW" to the user, the Event ID gets deleted. That way the load is distributed among the users.
Plus I can clear "NEW" events once in a while.
If you have a better way of implementing it (a "CONNECTION" object perhaps), please advise.
The post was written quickly, excuse the bad explanation.
Thanks in advance.
Unless you have loads of users I really doubt you'll have issues with just storing a "UserViewedEvents" table of some sort if you need to track if an individual user has seen an event.
Now, the quick and dirty way is to just track the user's last visit and then mark anything newer than said visit as "new".
I would like to store a list of integers in a row.
Each row represents a user.
The list can grow and shrink, but it has to be attached to a particular user.
What is the best data type for a list of numbers?
What do the numbers in the list represent? Do you need to do something with those numbers? It might be better to create a seperate table for the numbers that has a 1:N relation with your users table, but that really depends on what those numbers are and what you want to do with them.
I want to highlight new events that are added.
The list (comma separated) will be loaded into a variable, and if an event ID matches than the event hasn't been viewed by the user yet, it will be highlighted.
When the user did see an event, the event ID is deleted from the list, and the DB is updated.
do not use a comma-separated list for this, use a single column in a separate multi-row table
Could you give a bit more details please?
(I'm new to servers side and databases)
users ( id, name, etc.)
events ( id, title, datescheduled, etc.)
user_events ( userid, eventid )
bold indicates primary key, underline indicates foreign key
the user_events table will contain 1 row for each event for each user
in my opinion, it's easier to keep track of which events the user has seen, than to load them all up as unseen and later to delete the ones seen
Wouldn't the db become large?
The application is planned in a way that most users should see most events.
Events are attached to Projects, when the project is done it's moved to completed and all the data of new events is deleted.
The NEW EVENTS data is only needed for the active projects (a small portion).
I thought that it would be better to reduce the DB size by keeping only the UNSEEN EVENTS rather than all the events.
The event count will be very large, while the user count will remain relatively small.
I am just curious about optimizing the whole thing.
as for that other information, you should've presented that a lot earlier
i'm swinging around to think that unseen events might be better to store than seen ones, but not merely on the size consideration, but rather on the usage pattern
same table design, though
The app also uses the same model for permissions:
"Events" and "Projects" can have several "Owners" (user IDs).
Would suggest the same table design here?
You are a database guru