Page 1 of 1

Finding discrepancies in data with a query

Posted: Mon Oct 24, 2011 10:26 am
by mikeashfield
Hello,

I'm not sure if I can explain this as clearly as I would like, but my database is basically a rota system, let's say for example, that I have a table named tbl_shifts and a table named tbl_weeks for each of the 7 day fields in tbl_weeks (week_day1, week_day2, ... ) every shift must be covered from day 2 to day 6.

I have now got all my data in the DB and now just want to validate it for errors in the data entry (it was all manual) so I would like to construct some SQL queries that find weeks in the weeks table that aren't valid. I would also like to run a query to identify shifts that have a seconds value in the start or finish time. The time fields are formatted as H:i:m.

I built the first query I need, but it returns thousands of results (which definitely isn't the case).

Could anybody help?

Re: Finding discrepancies in data with a query

Posted: Mon Oct 24, 2011 12:51 pm
by califdon
Names of tables and fields don't tell much about what you intend them to represent. From the sparse information you have furnished, it doesn't look to me like your schema design is correct. I suggest that you describe what it is that you are trying to do, then probably we could recommend something for you to try. What is it that you want the database to do?

Re: Finding discrepancies in data with a query

Posted: Mon Oct 24, 2011 4:27 pm
by mikeashfield
Hello there,

Sorry about being so vague, I've attached my schema design as produced by MySQL Wokbench reverse engineering tool.
I basically want to be able to identify any week numbers where on days 2-6 all of the duty numbers are not covered. Hope this makes more sense.


Mike

Re: Finding discrepancies in data with a query

Posted: Mon Oct 24, 2011 8:30 pm
by califdon
Your schema diagram confirms that your underlying relational model is unlikely to yield useful results for anything I can think of. Whenever I see something being forced into a structure like your tbl_weeks, it is a bright red warning flag.

The only way I know to help you is to have you tell me in words just what this database is going to be used for. It needs to be something generally along the lines of this: "The database will record the descriptions of many duties, as well as on what shifts these duties must be performed. It will then permit the user to assign individuals to perform these duties during the shifts and display wherever nobody is assigned to a duty during a particular shift. ..." Your explanation should be perhaps 5 or 10 times that length and of course it must accurately describe what the database is intended to do--I'm only guessing. Then we can talk about what entities must be represented and what attributes each entity must have. That's the only way you can design a database of this kind.