mysqldatabasedatabase-design

specific queries for car park database design

We have two number plate reader cameras on the entrance and exit of the car park that will generate a CSV file when a detection takes place, which gets automatically loaded into the database, as well as a barrier on the entrance operated automatically by the camera's whitelist, generated and controlled from within the database and exported into a text file.

This is not a simple 3-table database:

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

The only relationship is "Whitelist plate-Plate_in & plate_out", where one plate in the whitelist could be seen many times within the in & out tables.

Specified queries with columns and logic:

  1. Whitelisted vehicles on site today (IF plate is 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 is NOT on Whitelist: Plate, Time_in, Time_out [if plate seen on OUT table, otherwise null])
  3. Whitelisted Vehicles time on site for today/last 7 days/last 30 days/last 90 days (IF plate on Whitelist: Plate, Description, Date_in, Time_in, Date_out, Time_out) This would have duplicate entries of same plates for multiple times on site.
  4. Non-whitelisted vehicles time on site for today/last 7 days/last 30 days/last 90 days (IF plate not on whitelist: Plate, Date_in, Time_in, Date_out, Time_out) This would have duplicate entries of same plates for multiple times on site.

How do I code each query?

Solution

  • I agree with Straweberry's post, you don't need two tables for in and out. You could have one for that like:

     tblVehicleEvent : ID (autonum/PK), Plate, EventDate, EventTime, EventType

    where EventType has either values of IN or OUT.

    Or you could have merge TimeIn and TimeOut and DateIn and DateOut like:

     tblVehicleEvent : ID (autonum/PK), Plate, DateIn, DateOut, TimeIn, TimeOut

    I think the second one would be easier for your query later on based on your requirements.

    The Queries would be like below:

    1. Whitelisted Vehicles on site today" (IF plate is on Whitelist: Plate, Description, Time_in,Time_out [if plate seen on OUT table today,otherwise null])

      SELECT W.Plate, Description, TimeIn, TimeOut FROM tblVehcileEvent V
      INNER JOIN tblWhitelist W
      ON V.Plate = W.Plate
      WHERE DateIn = curdate()

    2. "non-Whitelisted vehicles seen today" (IF plate is NOT on Whitelist: Plate, Time_in,Time_out [if plate seen on OUT table, otherwise null])

      SELECT Plate, TimeIn, TimeOut
      FROM tblVehicleEvent
      WHERE Plate NOT IN (SELECT Plate FROM tblWhitelist)

    3. "Whitelisted Vehicles time on site for today/last 7 days/last 30 days/last 90 days (IF plate on Whitelist: Plate, Description, Date_in, Time_in, Date_out, Time_out) this would have duplicate entries of same plates for multiple times on site

      SELECT W.Plate, Description, TimeIn, TimeOut
      FROM tblVehcileEvent V
      INNER JOIN tblWhitelist W
      ON V.Plate = W.Plate
      WHERE DateIn BETWEEN startDate and EndDate

    4. "Non-Whitelisted vehicles time on site for today/last 7 days/last 30 days/last 90 days (IF plate not on whitelist: Plate, Date_in, Time_in, Date_out, Time_out) This Would have duplicate entries of same plates for multiple times on site

      SELECT Plate, TimeIn, TimeOut
      FROM tblVehicleEvent
      WHERE Plate NOT IN (SELECT Plate FROM tblWhitelist)
      AND DateIn BETWEEN startDate and EndDate