Click here to Skip to main content
15,846,211 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:

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!
Mehdi Gholam 31-Jul-13 9:14am    
You really should consult your supervisor if you are not up for the task, he/she should set you on the right course.
Kschuler 31-Jul-13 14:00pm    
I think your problem is that you're trying to think of everything at once. Don't do that. Break the problem up into smaller bites or you will choke. First design your database in the way that makes the most sense. If I were you, I'd combine two of the tables. Instead of an IN and an OUT (which have virtually the same columns) I would have one table and just have a column in it that contains the value IN or OUT. Once you've got your database set, then work on each query that you need to do in your program one at a time. When you're stuck on how to get the query to work, post a question here on CodeProject with the query that you are using and details about what you want it to pull and why it's not working.
walterhevedeich 31-Jul-13 23:02pm    
Wish I could +5 this. It's actually better posted as a solution.
Daniel Daws 2-Aug-13 4:42am    
Thanks for your help!
Jörgen Andersson 3-Feb-14 6:41am    
If you have a camera operated barrier, how can you have non-whitelisted cars on site?

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900