Database Design Help! MySQL

See more:

SQL

MySQL

database

Hello,

My Company has asked me to develop a MySQL database as part of one of our current projects, with a front-end user interface developed in Visual Basic. I am fine with the Visual Basic side of things, but I'm really Struggling to wrap my head around the database portion of this task.

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 then gets automatically loaded into the database, as well as a barrier on the entrance operated automatically by the camera's "whitelist", which in turn is generated and controlled from within the database and exported into a text file.

Initially, I thought this would be a simple 3-table database as per the design below, but i am quickly realising that this is not the 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 only relationship I can think of would be:

**Whitelist plate-Plate_in & plate_out** where one plate in the whitelist could be seen many times within the in & out tables

----------
This has then been made more complicated by (and this is where my brain is really melting!) the queries that have been specified (brackets show columns and basic logic am thinking of for results):

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


What i really need help with is some ideas on how to sort out the Query code to get these working properly. This is one of the last main hurdles for me in this project. unfortunately, it's a hurdle the size of Everest. Any help you can provide would be greatly appreciated!