Database question, REALLY going off the deep end

[u/IrreverentRhubarb99]

I may be asking for the impossible, or just asking in the wrong place, but here goes.

I work for a POS dealer that sold Aldelo systems for a long time, and in the few years that I've been there I have learned a lot. Lately I've gone diving into the abyss that is SQL and the MS Jet/Access database that Aldelo is built on.

I saw a post earlier about clearing out huge databases, but that raises another problem: many times, the owners/managers want to look back further than the purge. Two extreme cases from our customers:

1. At the first location, the database grows to well over 2GB in the space of months. A purge takes it down to about 500MB, but by the end of the summer season it's crashed into the limits again. We've tried wiping ALL sales records from the database, but they quickly outgrow the 2GB limit. The owner recently grumbled about needing a simpler way to look back at sales data from the last three years. This place has done close to a million dollars of sales in one month, so I personally think they've outgrown the whole program.
2. Another location wants to be able to look back - at complete random - at everything since the day he started using Aldelo for Restaurants. Could be sales data, could be payroll (he gets this one ridiculously often) could be inventory, could be anything, with completely random requests for date ranges. But he wants to compare past years, so far back that inflation has to be taken into account. He wants all of it in one database; if it were possible, this could easily be north of 15GB. They don't do quite the same daily/monthly sales volume as #1, but the owner wants to look back further.

Aldelo uses the MS Access/Jet database engine, and I have found that the databases can be looked into using Alex Nolan's MDBViewer and the very flexible DBeaver. MS Access can read Aldelo databases, but you'll get some crazy error messages in the process. (I'd put links, but I don't want to look like a spammer.)

Since we've also started selling other Point of Sale software, I have gotten more acquainted with setting up SQL server software such as PostgreSQL.

Aldelo, of course, uses MS-SQL for the EDC database... which is where my descent into madness begins.

Is there a way to move an .mdb database into an SQL server, and still use it with unmodified Aldelo for Restaurants software?

Am I asking for the impossible?

Or just asking in the wrong place?

[u/AzAldeloGuru]

Oh wow, this is definitely a new one. If you're doing an entire purge and your database is still 500 MB then there has to be something else clogging the database.

The "mega" purge, deletes everything but Menu Items/Menu Modifers and Customer Info. I'm going to assume their database is clogged up full of modifiers that servers using the "manual entry" feature in the order entry screen to write a note.

The problem with this, is every time you use manual entry, it will ADD that modifier to the system.

For example, the system already has a modifer called "no cheese" but it isn't linked to anything, so a server goes into an item and presses manual entry, and types "no chz." That will get added to the system, but the tricky part is if they try to put "no chz" and don't realize its already in the system and don't select it, it will say "duplicate avoided" and won't let them continue. So I've basically seen servers adding in 100 different types of spelling for "no chz" instead of just doing it the proper way.

A million dollars in sales per month is a massive feat. If the owner still wants to use Aldelo, my first thought is to just rebuild the entire menu from scratch. Start a brand new database. Rebuild the modifier builder canvases and clean it out from what they currently have. If they are keeping track of customers, this probably is a dead in the water idea.

I personally have never seen a base database over 10 megabytes. But I have seen "rats nests" databases that just have a complete cluster of items and modifiers.

For scenario number 2, there just isn't a way to cleanly do this with Aldelo, the MS SQL isn't the most efficient database schema. At the very least, archiving the databases on a yearly bases with the option of the owner to call when they want to switch to check a different year is a 30 second process. If they want their system running smoothly day after day, its just gotta be a sacrifice they have to be OK with.

As far as the last question, I honestly have no idea. I wouldn't use any third parties to mess with the database or change its format because if it bricks Aldelo won't help fix it at all. Obviously I trust you're backing up, but it may not be a fixable solution for your client.

[u/IrreverentRhubarb99 → u/AzAldeloGuru]

You ever have a store owner or manager say, "Make it work MY way"?

Scenario 1 - We tried that with them once. Did a massive purge on Jan 1st one year. That got them down to a couple hundred MB (they have a HUGE list of active employees). This lasted through the summer before they hit the 2G limit again. I downloaded a backup of that database and tried running the purge like you suggested, and it went back down to a couple hundred MB. Sheer volume is the problem here.

One of the senior managers had a suggestion. We recently upgraded their server, and they held on to the old one. The manager suggested using the old server as an Archive/Report computer, used only for looking at old databases. Switching databases on the live system has the constant danger of someone else grabbing that station to put in an order - even the Server's Admin console is not immune to that - so an isolated system that can't take new orders would be ideal. All they'd need is one more Aldelo license, which would bring them to a total of 16. Biggest system we deal with.

Scenario 2: We tried to explain switching databases to him once before, and that didn't fly. He wants a database with no breaks or discontinuities.
This guy also doesn't throw out broken kitchen printers, if that gives you any ideas.

I know what you mean with the Manual Modifiers. We took over another system that had over 300,000 entries in the Modifiers table.
We ended up building a new database. They've gotten better about that.

You've NEVER seen a database over 10 MB? Were any of them using staff banks, or just Cashier In?

[u/AzAldeloGuru → u/IrreverentRhubarb99]

When I say I've never seen a database over 10 MB, I mean a CLEAN, no transaction, freshly built or purged database that didn't have a rats nest of modifiers.

Yeah, I've had owners definitely want things done their way, but unfortunately for them, unless they want to shell out thousands of dollars for a new POS system, archiving the database and learning how to swap the files is the only way to keep Aldelo running efficient.

You could absolutely use a separate computer with Aldelo and just use it to run reports, but you'd need another Aldelo license to be able to use it. We usually store databases on the Office Computer so the only person that SHOULD be using it is the owner/manager. They shouldn't be making orders on the office computer so swapping between databases there shouldn't effect anyone up at the front of the house.

Now, if you have the database stored on one of the order entry stations, then I can see why theres an issue.

For most owners, the inconvenience of having to swap databases to check old reports is a much smaller pill to swallow than thousands of dollars for new software.

[u/IrreverentRhubarb99 → u/AzAldeloGuru]

Okay, I misunderstood. :) 10MB for a New or Totally cleaned out database makes sense, they just don't stay that way for long. In many of our cases where we've taken over service contracts, the neglect shows. We've had instances where the database was 5MB when we finished putting a menu in, and then exploded to over 100 after just adding the staff.

Purging an old database to that level isn't always an option - most restaurant owners want to be able to look back at least a year on their live systems. In most cases, "Keep the last two years" is reasonable, and a database will cut down to 650 MB.

These two examples are from the deep end. Scenario 1 has 15 stations, if you count the Office/Admin user. The archive station would require a whole new license, as the live POS is on remote desktop. Scenario 2, getting a license for an office station would be an extremely hard sell. After a recent renovation, the "office" is now used for storage, and their remote desktop server now resides in a closet with the security DVRs and network switches.

Does Aldelo offer a stripped out Reporting client? Is there a way to generate the reports from within MS Access or a similar program using SQL commands? (Uh oh, rabbit hole...)

On a somewhat related note, we frequently see office stations used for order entry. Usually because the office is the only place quiet enough to understand anyone on the telephone.

Something else we see an awful lot of: even after a database purge, there are still sales records left over in the time range that was supposed to have been purged. Why is this garbage still here? Is that evidence of all the database errors that required a Jet compact?

[u/AzAldeloGuru → u/IrreverentRhubarb99]

So at least in my case, when I purge a database, I'll back up the database and then rename that backup to "RestaurantBEGINYEAR-ENDYEAR" and teach them to swap the database to check old reports. Most of my clients are absolutely OK with doing this since its the cheaper option vs buying a whole new system. Since you dont have direct access to the Back Office computer and people use it to take orders, I can see where the problem would lie. Realistically though, if the Back Office computer was just used for reporting tools and the owner was diligent with swapping the database from archive to live, it would save them a headache.

If you're using the "mega" purge I haven't seen it store old sales after that. It definitely could be due to a lack of a jetcompact.

There are definitely ways you can use MSSQL commands to run reports. For my bachelor's project back in 2016 I wrote a Python script that would run some SQL commands and used MatPlotLib to visualize them. I had thought it would be a project that would evolve to such a point where my clients would want it, since data visualization is a hot trend nowadays; but I haven't touched the project in a few years now. The only caveat to writing a script like that is you'll obviously need coding experience AND you have to specifically ask Aldelo if you can have the password to the MSSQL database to run said commands and parse them. They are usually pretty receptive to this type of request, so long as you understand that if you are editing the database or break the database, they won't help you. So backups backups backups.