This is my first question here, sorry if I'm breaking any etiquette. I'm kind of into coding, but sometimes my brain is hard to swallow path of logic steps.
Currently I'm working on my own small web-app, where I have public events, and I'm making my own guestlist.
By the time, I've solved these things:(I think so)
To Do:
Problem:
Currently I'm getting all attendees from JSON string, then converting to array, putting it all in database. I can't decide on SQL logic.
I have all list with people - json->array->db then it reads from db and show which is checked which one not, like comparing with table that is from JSON.
Current algorithm is - getting json, and in foreach cycle, every time I load it writes in DB, using INSERT IGNORE it ignores if it's same userid, so I have db of all attendees.
How to arrange my database? I'm thinking about making tables:
guests - USERID ; EVENT ID; NAME; [for huge list of all people] checkins - USERID; CHECKEDEVENTID; DATETIME; [for getting stats]
My goal is to make "Checking In" door-app, so in the end I see, that those and those users are attending more on those kind of events, than these one...
So how could I make like stats, like - EVENT - attended Y people of X, and more global SQL queries, like, USER Y came to EVENTS A,B,C. Or, most checkins happening at timespan [probably some bars or chart]....
Should I make for each event new table to store all guest there to see all attendee statistics, and checking table for checkin stats?
For the what you refer to as the "Check" feature, it sounds like you want (roughly*) the following tables:
create table users
(
userid float NOT NULL,
username varchar(64)
);
create table events
(
eventid float NOT NULL,
eventname varchar(64),
eventstart date,
eventlength float
);
create table checkin_activity
(
userid float not null,
eventid float not null,
checkin_time date
);
* This is a highly simplified database schema. You'll want to make sure you add the necessary keys, constraints, etc., and make sure the data types on your columns are appropriate. I didn't give that much thought with this quick example.
Using the entries in the USERS and EVENTS tables, you'll populate the CHECKIN_ACTIVITY table with what you refer to as the "Check" button. You can join queries against these tables as needed to run reports and so on.
NOTE: You mention:
Current algorithm is - getting json, and in foreach cycle, everytime i load it writes in DB, using INSERT IGNORE it ignores if it's same userid, so i have db of all atendees
You should avoid writing to the database within a for loop (into a table I didn't account for above; let's call it the EVENT_ATTENDEES table). Instead, build an INSERT ALL query and executing it once so you're not hitting the database's transaction handler n times.
INSERT ALL
INTO event_attendees (eventid, name) VALUES (1, 'John')
INTO event_attendees (eventid, name) VALUES (1, 'Jane')
INTO event_attendees (eventid, name) VALUES (1, 'Colin')
SELECT * FROM dual;
This is especially important if this kind of load is something you'll be doing often.