mysql - Car Park Database Design -

we have 2 number plate reader cameras on entrance , exit of car park generate csv file when detection takes place, gets automatically loaded database, barrier on entrance operated automatically camera's "whitelist", in turn generated , controlled within database , exported text file.

initially, thought simple 3-table database per design below, realising not case:

my initial designs:

tbl_in :      id (autonum/pk), plate, date_in, time_in     tbl_out:      id (autonum/pk), plate, date_out, time_out     tblwhitelist: plate(pk), country code, description 

currently,the relationship can think of be:

whitelist plate-plate_in & plate_out 1 plate in whitelist seen many times within in & out tables

---

this has been made more complicated (and brain melting!) queries have been specified (brackets show columns , basic logic thinking of results):

1. "whitelisted vehicles on site today" (if plate on whitelist: plate, description, time_in,time_out [if plate seen on out table today,otherwise null])
2. "non-whitelisted vehicles seen today" (if plate not on whitelist: plate, time_in,time_out [if plate seen on out table, otherwise null])
3. "whitelisted vehicles time on site today/last 7 days/last 30 days/last 90 days (if plate on whitelist: plate, description, date_in, time_in, date_out, time_out) have duplicate entries of same plates multiple times on site
4. "non-whitelisted vehicles time on site today/last 7 days/last 30 days/last 90 days (if plate not on whitelist: plate, date_in, time_in, date_out, time_out) have duplicate entries of same plates multiple times on site

what need ideas on how sort out query code these working properly. 1 of last main hurdles me in project. unfortunately, it's hurdle size of everest. can provide appreciated!

i agree straweberry's post, don't need 2 tables in , out. have 1 like:

 tblvehicleevent : id (autonum/pk), plate, eventdate, eventtime, eventtype 

where eventtype has either values of in or out.

or have merge timein , timeout , datein , dateout like:

 tblvehicleevent : id (autonum/pk), plate, datein, dateout, timein, timeout 

i think second 1 easier query later on based on requirements.

the queries below:

1. whitelisted vehicles on site today" (if plate on whitelist: plate, description, time_in,time_out [if plate seen on out table today,otherwise null])

   select w.plate, description, timein, timeout tblvehcileevent v
   inner join tblwhitelist w
   on v.plate = w.plate
   datein = curdate()

2. "non-whitelisted vehicles seen today" (if plate not on whitelist: plate, time_in,time_out [if plate seen on out table, otherwise null])

   select plate, timein, timeout
   tblvehicleevent
   plate not in (select plate tblwhitelist)

3. "whitelisted vehicles time on site today/last 7 days/last 30 days/last 90 days (if plate on whitelist: plate, description, date_in, time_in, date_out, time_out) have duplicate entries of same plates multiple times on site

   select w.plate, description, timein, timeout
   tblvehcileevent v
   inner join tblwhitelist w
   on v.plate = w.plate
   datein between startdate , enddate

4. "non-whitelisted vehicles time on site today/last 7 days/last 30 days/last 90 days (if plate not on whitelist: plate, date_in, time_in, date_out, time_out) have duplicate entries of same plates multiple times on site

   select plate, timein, timeout
   tblvehicleevent
   plate not in (select plate tblwhitelist)
   , datein between startdate , enddate