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


Comments

Post a Comment

Popular posts from this blog

c++ - End of file on pipe magic during open -

i have c++ application in starting process(wireshark) following. if (fp == null){ fp = popen(processpath, "r"); //processpath process want start if (!fp){ throw std::invalid_argument("cannot start process"); } fprintf(fp, d_msg);//d_msg input want provide process } else if(fp != null){ fprintf(fp, d_msg); } the problem when execute c++ application, start wireshark error end of file on pipe magic during open what should avoid that? also tried using mkfifo create named pipe , execute it. used this: if (fp == null){ system("mkfifo /tmp/mine.pcap"); fp = popen("wireshark -k -i /tmp/mine.pcap", "r"); if (!fp){ dout << "cannot start wireshark"<<std::endl; throw std::invalid_argument("cannot start wireshark"); } input = fopen("/tmp/mine.pcap", "wb"); fprintf(input , d_msg); fclose(input)...
Read more

basic authentication with http post params android -

i doing basic authentication passing username , password , using basicnamevaluepair sending post params response service. my method: public stringbuilder callservicehttppost(string username, string password, string type) { // create new httpclient , post header httpclient httpclient = new defaulthttpclient(); httppost httppost = new httppost(webservice + type); httpresponse response = null; stringbuilder total = new stringbuilder(); try { url url = new url(webservice + type); /*string base64encodedcredentials = base64.encodetostring((username + ":" + password).getbytes(), base64.url_safe | base64.no_wrap);*/ string base64encodedcredentials = "basic " + base64.encodetostring( (username + ":" + password).getbytes(), base64.no_wrap); httppost.setheader("auth...
Read more

data.table making a copy of table in R -

i doing this: myfun <- function(inputvar_vec){ # inputvar_vec input vector # # result = output vector return(result) } dt[, result := lapply(.sd, myfun), = byvar, .sdcols = inputvar] i getting following warning: warning message: `in `[.data.table`(df1, , `:=`(prop, lapply(.sd, propeventinlastk)), : invalid .internal.selfref detected , fixed taking copy of whole table, := can add new column reference. @ earlier point, data.table has been copied r (or been created manually using structure() or similar). (and more stuff) .... ` my guess because stacking result vectors (after operation), copy being made? can suggest method remove warning? have done using apply functions , thought should extendable here too. my other question is: can pass chunk of rows data frame (subsetted using statement), , call function myfun operate on that? adding example requested # generate data n = 10000 default=na value = 1 df = data.table(id = sample(1:5000, n, replace=true), ...
Read more