Simple End-User SQL Builder
Posted: Wed Jul 09, 2008 11:29 am
I'm trying to build a form for non-technical users to build a targeted audience based on certain criteria/user attributes (Role, UserID, Birthdate, etc.). Because the users are non-technical, it needs to be simple logic. Therefore, the SQL being produced from the form will be very limited, using AND and OR logical operators, and =, !=, >, < comparison operators. This portion of the form is pretty straight-forward design. My problem is that I need to allow grouping of statements, and I'm struggling to come up with good usability. I tried searching for something similar out there, but with no luck.
Another thing I should add, is that I will be saving these queries in a database.
Here's an example of a basic WHERE CLAUSE that I'd like to produce from the form:
WHERE Role = 'abc' AND (UserID != 'xyz' OR Dept != 'defg')
I have a couple ideas of how to create this form.
1) Build a form where the end-user has the freedom to build the logic one piece at a time They would be fully responsible for putting together the correct structure/logic using any of the components available, including grouping.
PROs: This would probably be the easiest, most straight-forward form to build (in terms of development). The queries could be stored in a single database field.
CONs: It wouldn't be the easiest to use. The end user would have to understand what they're doing. I'd have to have a considerable amount of error checking/validation for the entire statement as a whole when they submit it.
2) Take a more structured approach, where the end user's available components for the statement may be limited based on what they already have used. As far as grouping, I was thinking of having a checkbox under each piece. The end user then selects a starting piece and an ending piece. Everything between is grouped together.
PROs: This would I think be a little easier to use, forcing the statement to be logical while they build it.
CONs: May need javascript/ajax because each piece would require validation and updating of the possible choices. Reloading the screen several times may interfere with usability. Not sure about that. Also, the db storage structure will most likely be a little more complicated. I'd probably have to save each piece of the statement separately.
I would appreciate any feedback on this. I would especially appreciate any links to other working solutions or concepts.
Another thing I should add, is that I will be saving these queries in a database.
Here's an example of a basic WHERE CLAUSE that I'd like to produce from the form:
WHERE Role = 'abc' AND (UserID != 'xyz' OR Dept != 'defg')
I have a couple ideas of how to create this form.
1) Build a form where the end-user has the freedom to build the logic one piece at a time They would be fully responsible for putting together the correct structure/logic using any of the components available, including grouping.
PROs: This would probably be the easiest, most straight-forward form to build (in terms of development). The queries could be stored in a single database field.
CONs: It wouldn't be the easiest to use. The end user would have to understand what they're doing. I'd have to have a considerable amount of error checking/validation for the entire statement as a whole when they submit it.
2) Take a more structured approach, where the end user's available components for the statement may be limited based on what they already have used. As far as grouping, I was thinking of having a checkbox under each piece. The end user then selects a starting piece and an ending piece. Everything between is grouped together.
PROs: This would I think be a little easier to use, forcing the statement to be logical while they build it.
CONs: May need javascript/ajax because each piece would require validation and updating of the possible choices. Reloading the screen several times may interfere with usability. Not sure about that. Also, the db storage structure will most likely be a little more complicated. I'd probably have to save each piece of the statement separately.
I would appreciate any feedback on this. I would especially appreciate any links to other working solutions or concepts.