ETA: I apologize for the length and meandering of this post. I've been trying to wrap my head around this stupid project and all its rules for weeks.

Hi guys,

I've spent the last few weeks trying, with a couple of different tools, to build a reasonably user-friendly database with reporting to replace a shockingly convoluted spreadsheet that my colleagues have been using to keep track of various advertising campaigns, contacts, accounts, and so on. It's a real nightmare.

One important issue that confounds my colleagues' attempts to build this thing as a spreadsheet is that there are complicated constraints on some items. For example:


I built my first DB using Access because I had it handy, but as soon as I got to the Form-building stage I kind of bounced off of it in favor of something a little more idiot-friendly. I never quite got to the point of trying to restrict the input according to the ridiculous rule above.

Next I tried building this application using Zoho Creator. I even implemented that ludicrous rule described above (I ended up having to create a relationship between Hubs and the lookup table for Widget types and, then query... nevermind, it's a nightmare). The forms were decent, easy for my colleagues to use, and it was reasonably easy to translate my database schema from Access to their system of Forms. Until I got to the stage where I need to start actually building reports...

And their report builder won't let you build reports that connect tables more than one relationship away from the current table! So if you have a DB where you've got tables A, B, C, D and relationships A->B, B->C, C->D, it's impossible to build a report that joins data from all four of them. If I need information about D in a report that starts in table A, I have to write a script that copies data from D into C according to that relationship, then a script that copies data from C to B according to that relationship, and then my report can include data from A and B. Avoiding doing exactly this is the reason relational databases were invented in the first place! (I bitched at length about this to a buddy and he explained that their reasoning is probably to avoid "N+1" or "N vs. 1" problems when deciding how to structure queries for reports)

Anyway, so much for Zoho Creator. If I want to keep using it I either have to write a lot of really bad scripts that turn my database into a crappy, ugly spreadsheet, or I have to mash a bunch of my tables together to make crappy, ugly spreadsheets to reduce the number of relationships so that Zoho can make intelligible reports out of them.

So, here it is:

Do y'all know of any relational database front-ends that will let me implement the kinds of complex input constraints I need and have an adequate interface for adding/updating records and have a decent way to turn queries into reports?

I know the basics of T-SQL and I can figure out how to do what I need to do in other flavors of SQL, and I can write sub-idiot-grade javascript and python (...and Deluge, thanks to frigging Zoho Creator). I know the right way to do this is to build a custom application to run the queries and build the forms and their input constraints, but our one programmer has lots of much more important stuff to do.

Any suggestions? I downloaded dbForge studio just to see if I could start bashing together the basics of my tables and stuff in MySQL (I don't want to experiment on the company's MSSQL servers, because they do important stuff). For the frontend I was poking at myDBR, which has very pretty report-generating tools and forms, but I don't know if it can do the bizarre input rules I need.

Thanks!

I am not a database expert but have done many database projects over the last 15 years usually using perl and mysql. The closest thing I know of that will do what you want is django which is a framework on top of ruby. django, if I remember correctly, handles the database table structure and even table structure changes for you automatically. It allows you to be general about the data you are storing but also as specific as you want to be. It just uses intelligent defaults when writing sites with it. If you change your data structures it automatically modifies the database on the backend with no effort on your part.

Thats my best answer.

PS - I like your bunny avatar.

Thanks very much!

That's interesting. I always just thought Django was for making websites, but I'm only getting that secondhand from occasionally hearing competent friends talk about it. I'll poke at it! http://stackoverflow.com/questions/6300200/using-django-as-a-custom-database-management-tool - it seems like this guy on Stack Overflow might be trying to do a similar thing.

ETA: Hey, Django actually runs on Python, about which I have read approximately one half of a textbook, so I might be able to get in there and make a real proper mess of things!

Thanks! It's cropped out of a picture I commissioned from Teagan Gavet.

Whoops! Thought django was ruby! Been a while since I looked at it.

All I can tell you was that it was super fast to get some database driven web forms up and running.

No sweat! You wouldn't happen to know of a good source for the "Total Asshole's Guide to Django" would you? I might as well start from zero.

Nothing I can personally recommend. Whatever you can dig up will probably be as good as anything I can recommend. O'reilly books always seem to be a good resource.

We use MS Excel linked with ML SQL BI on a SSIS environment. It allows users to pull almost any business data and create a report on it.

That's interesting. We've all already got office and the requirements for this DB are probably not so heavy that it's going to bog down our MSSQL servers (plus, we're friggin paying enough for the licenses already, might as well get some more use out of them...).

Does it also permit users to add or edit data right there from Excel?

ETA: also, can you implement ludicrous, byzantine input constraints?

Op, for years we used spreadsheets to track our junk... and they were ok but not the best... especially when we wound up having the potential for 256 individual spreadsheet to track our junk with. So we said enough.

We moved the spreadsheets to Access. It "worked" for some of what we wanted but not everything. So we moved to SQL Express (because it was free license)

Having a true SQL backend made life much easier for us to manage our data with.
The front end we used to access the data with was built within SharePoint and worked... until the SharePoint install was upgraded (but our front end was never ported over to use the newest version of SharePoint we had), the project we were supporting was canceled, and we moved onto other things.

Now, a few years later, the project has been resurrected under another name but with the same general specifications... plus a whole lot more. Fun times.

Basically I said all that to say that SQL as a back end works well.

Make a web front end to enter data and enforce constraints, and use Crystal Reports for reports.

Crystal and I have a love/hate thing going back years but yeah this is a solid and easy way to get where it looks like the OP wants to go.




Posted Via AR15.Com Mobile

I ended up just pressing on and implementing the suck-ass kludges that Zoho Creator support suggested. It got this janky app into the hands of my colleagues much faster than my having to learn real SQL and some other kind of thing to implement a web front end myself would have been.

Basically, ended up having to write a bunch of scripts in their (very simple) language that restrict or populate the fields used to establish table relationships, and then a bunch of other scripts that copy data from one table to another so that a report "centered" on one table can get data tables more than one relationship "away".

It's a bad database, but it's adequate for now.

Thanks again for all the help! I'll have to keep poking at the suggestions and try to learn about them - I know I'll eventually have to do something like this again, and it'll be nice not to have to rely on some jank-ass technology that hamstrings itself.

Is that still a thing? Man, I haven't done CR in almost 20 years. I agree with the love-hate thing.

It's been about 10 years for me.  I have no idea how it's being used in the current marketplace.

I know that whoever is using it, has a love/hate relationship with it.

Ain't that the truth.

I'll admit that it was pretty good back in the day but it wasn't very fun to work with. Two implementations that stand out to me are a reporting system I wrote back in the mid- to late-90s for the Florida CCW system that we were re-writing so it'd be automated and you Floridians could get your licenses faster (you're welcome) and another A/P system for booking restaurant fixtures for Darden Restaurants (Olive Garden, Bahama Breeze, etc). The latter job also entailed integrating with the AS/400 and there weren't many good tools back then so I had to resort to (ugh) screen scraping.

I remember someone asking for access to Microsoft Power BI at my work,  the data practices lawyers said no.

https://powerbi.microsoft.com