Feasibility of mobile app based on customer/product database

RainyDay

Folks

I'm an old programmer at heart, but I know little or nothing about mobile apps. I have a need for a mobile app that will allow a mobile user to review and update customer and product data, currently held in a central database. I've seen the tools for mobile apps that can create standalone apps, but I haven't seen anything that can use an existing database. My current data is in MS Access, though I'm not wedded to this by any means. I'm looking for some kind of toolkit that will allow me to do something similar to MS Access - queries, forms and reports - on a mobile 'distributed data' basis. I'd also like to be able to use some of the more advanced mobile facilities, liking picking up current GPS location and using that to find nearby customers, and taking a photo which is then logged to that customer. Does such a toolbox exist?

Thanks in advance

Tom Dunne

I would imagine a more robust database would be the first step, such as MS SQL Server, MySQL or Oracle.

There are two steps that would then probably run in parallel - creating a web service that would the middle tier between the app and the DB, and creating the mobile app itself. The app would not store any data locally, everything would be sent up to the central database via the web service.

So you would essentially have your standard three-tier architecture.

RainyDay

Thanks Tom - I was hoping that there might be some kind of toolkit or cloud service that would take me part of the way along this road, rather than having to build everything from scratch myself. Any ideas?

Tom Dunne

RainyDay wrote: »

Thanks Tom - I was hoping that there might be some kind of toolkit or cloud service that would take me part of the way along this road, rather than having to build everything from scratch myself. Any ideas?

No ideas, I'm afraid

RainyDay

Tom Dunne wrote: »

No ideas, I'm afraid

Thanks for getting back to me. If others have any suggestions as to how I might move forward with this, I'd love to hear more.

jgh_

RainyDay wrote: »

Thanks Tom - I was hoping that there might be some kind of toolkit or cloud service that would take me part of the way along this road, rather than having to build everything from scratch myself. Any ideas?

I believe services such as Parse.com will do what you are looking for.

RainyDay

jgh_ wrote: »

I believe services such as Parse.com will do what you are looking for.

Thanks, that's a very interesting service, and goes a fair bit down the road I'm looking for. I've spent a bit of time playing around with it today. I'm not sure that the data structure would handle a high volume of data - it doesn't seem to have any indices or any true relational structure. Also, you really need to be a 'proper' app developer to use the service, as it uses API calls. I was really hoping for some kind of Access-type app development environment, but maybe I'm ahead of the curve here.

But a big thanks for pointing it out. Again, if others are aware of other similar services, please shout.

ChRoMe

RainyDay wrote: »

Also, you really need to be a 'proper' app developer to use the service, as it uses API calls. I was really hoping for some kind of Access-type app development environment, but maybe I'm ahead of the curve here.

But a big thanks for pointing it out. Again, if others are aware of other similar services, please shout.

I'm afraid that any solution to this is going to require API calls of some sort. I wouldnt be too afraid of them REST calls are simple HTTP verb requests and can very extremely easy to consume and create. Have a look at Java's "Jersey" lib as an example.

RainyDay

ChRoMe wrote: »

I'm afraid that any solution to this is going to require API calls of some sort. I wouldnt be too afraid of them REST calls are simple HTTP verb requests and can very extremely easy to consume and create. Have a look at Java's "Jersey" lib as an example.

Thanks for the update.

Graham

Without knowing much about the project you have in mind, Filemaker Pro may be an option for you.

The Corinthian

RainyDay wrote: »

I'm an old programmer at heart, but I know little or nothing about mobile apps. I have a need for a mobile app that will allow a mobile user to review and update customer and product data, currently held in a central database. I've seen the tools for mobile apps that can create standalone apps, but I haven't seen anything that can use an existing database. My current data is in MS Access, though I'm not wedded to this by any means. I'm looking for some kind of toolkit that will allow me to do something similar to MS Access - queries, forms and reports - on a mobile 'distributed data' basis. I'd also like to be able to use some of the more advanced mobile facilities, liking picking up current GPS location and using that to find nearby customers, and taking a photo which is then logged to that customer.

Essentially, you want an app that acts as a fairly feature rich client for a remote database; not only to the data, but to form layouts / views and queries / stored procedures.

You probably should also specify what type of mobile app you mean; iPhone, Android, etc.?

Does such a toolbox exist?

I don't think so. Looking at Android, there's this which is specifically orientated twoards Access, but it requires a local MDB on your SD card (which means you'll have to synchronize) and may not be up to scratch where it comes to the features you want.

This, on the other hand, appears to support both Access and remote DB access, but beyond that I have no idea if it's any good.

Overall, I'm surprised there's no app out there that does something like this, but then again, I've not looked very hard.

Another option, of course, is to use a Web-based interface, optimized for mobile browsers, and just develop it as an n-tier site.

ChRoMe wrote: »

I'm afraid that any solution to this is going to require API calls of some sort. I wouldnt be too afraid of them REST calls are simple HTTP verb requests and can very extremely easy to consume and create. Have a look at Java's "Jersey" lib as an example.

There's no need for a separate API - after all you can always connect to the database directly and interact with it using SQL.

ChRoMe

The Corinthian wrote: »

There's no need for a separate API - after all you can always connect to the database directly and interact with it using SQL.

Well there isint a need, but its a really good idea! You are opening yourself up to a world of potential pain having multiple mobile devices interacting with the database, the more I think of it the worse it sounds tbh.

jgh_

ChRoMe wrote: »

Well there isint a need, but its a really good idea! You are opening yourself up to a world of potential pain having multiple mobile devices interacting with the database, the more I think of it the worse it sounds tbh.

Haha yeah, I think the better approach would be to have a public-facing API with limited capabilities and each client gets its own API key into that rather than trying to manage a ton of different DB users and giving them direct access to the database. That just sounds like a security nightmare at the very least.

The Corinthian

jgh_ wrote: »

Haha yeah, I think the better approach would be to have a public-facing API with limited capabilities and each client gets its own API key into that rather than trying to manage a ton of different DB users and giving them direct access to the database. That just sounds like a security nightmare at the very least.

How does a public API, giving each user a separate login (that's what an API key is at the end of the day and an admin has to manage those too) differ from giving each user a separate DB user account, with specific permissions (only to run stored procedures) to the database?

Personally, I think it comes down to who and what the DB is for. A custom job for an SME, direct database access would be fine. Something more scalable and configurable, on the other hand, may well benefit from middleware - and not just for security reasons.

The more interesting question is where is the business logic to the client interface kept?

jgh_

The Corinthian wrote: »

How does a public API, giving each user a separate login (that's what an API key is at the end of the day and an admin has to manage those too) differ from giving each user a separate DB user account, with specific permissions (only to run stored procedures) to the database?

Personally, I think it comes down to who and what the DB is for. A custom job for an SME, direct database access would be fine. Something more scalable and configurable, on the other hand, may well benefit from middleware - and not just for security reasons.

The more interesting question is where is the business logic to the client interface kept?

I have to admit, I have never seen anybody advocate giving end-users direct database access before. While I don't immediately see anything particularly disastrous about creating a stored proc layer and just giving end-users access to it, it kinda goes against everything I've ever been taught/read when it comes to doing this kind of thing. Being that I'm not much of a DB/web app guy, I'll just say you're right and I'm probably misguided in my thinking.

ChRoMe

The Corinthian wrote: »

How does a public API, giving each user a separate login (that's what an API key is at the end of the day and an admin has to manage those too) differ from giving each user a separate DB user account, with specific permissions (only to run stored procedures) to the database?

Personally, I think it comes down to who and what the DB is for. A custom job for an SME, direct database access would be fine. Something more scalable and configurable, on the other hand, may well benefit from middleware - and not just for security reasons.

The more interesting question is where is the business logic to the client interface kept?

The most obvious problem with the direct access approach is updates. Say you want to modify/create new functionality. You are going to have a complete mess doing that purely on the client side. You cant guarantee all mobile clients are on the new version for starters... its terrible engineering practice.

Separation. Of. Concern.

The Corinthian

jgh_ wrote: »

I have to admit, I have never seen anybody advocate giving end-users direct database access before. While I don't immediately see anything particularly disastrous about creating a stored proc layer and just giving end-users access to it, it kinda goes against everything I've ever been taught/read when it comes to doing this kind of thing. Being that I'm not much of a DB/web app guy, I'll just say you're right and I'm probably misguided in my thinking.

No, you and ChRoMe could well be right, I'm just looking at it from a general point of view, where in theory, there's no superficial reason against the idea. Once you start digging further, especially if you start examining the security implications of exposing some database versions to the World, then it gets way more complicated.

In short - until the OP comes back to the thread - I'm just shooting the breeze and making some entertainment for myself.

ChRoMe wrote: »

The most obvious problem with the direct access approach is updates. Say you want to modify/create new functionality. You are going to have a complete mess doing that purely on the client side. You cant guarantee all mobile clients are on the new version for starters... its terrible engineering practice.

I'm not entirely sure what mess you mean on the client side. Almost all the business logic is in the database - you're accessing stored procedures only, if you remember.

The only thing that ultimately has to be handled in the client, really, is presentation, which is why I earlier wrote that the more interesting question is where is the business logic to the client interface would be kept, as whether you're connecting to some HTTP-based middleware or directly to the database, this is going to be the case - you're going to have to deal with the 'mess' on the client either way.

ChRoMe

The Corinthian wrote: »

No, you and ChRoMe could well be right, I'm just looking at it from a general point of view, where in theory, there's no superficial reason against the idea. Once you start digging further, especially if you start examining the security implications of exposing some database versions to the World, then it gets way more complicated.

In short - until the OP comes back to the thread - I'm just shooting the breeze and making some entertainment for myself.

I'm not entirely sure what mess you mean on the client side. Almost all the business logic is in the database - you're accessing stored procedures only, if you remember.

The only thing that ultimately has to be handled in the client, really, is presentation, which is why I earlier wrote that the more interesting question is where is the business logic to the client interface would be kept, as whether you're connecting to some HTTP-based middleware or directly to the database, this is going to be the case - you're going to have to deal with the 'mess' on the client either way.

First, yes all we are doing is shooting the ****.

It certainly could be done with the direct database connections, but again I just feel like its doing too much on the client side. Having a mobile application dealing directly with result sets and the like just feels very wrong. It also causes problems for logging/analytics. If you have all clients coming through on the same db credentials.. it would be difficult if not to measure individual usage. It also ties you to a specific datasource.. which can be irritating down the line

In summary, for the scope of a project of this size, you could very well get a reasonable solution without writing an API. However I've just seen it so many times where the PM/client says "oh its just a small thing, get it done quick/dirty" and then later on come back and say "Oh right, it worked great we want to roll it out to all of Europe".

The Corinthian

ChRoMe wrote: »

It certainly could be done with the direct database connections, but again I just feel like its doing too much on the client side. Having a mobile application dealing directly with result sets and the like just feels very wrong.

It's not like it's getting anything different to what it would get if you were returning the data as XML or whatever. The actual 'clever stuff' is happening in the stored procedure in the database - the client is just rendering the same data in the same way as if it had received it from middleware.

It also causes problems for logging/analytics. If you have all clients coming through on the same db credentials.. it would be difficult if not to measure individual usage.

Who says that all the clients have to log in using the same user creds?

It also ties you to a specific datasource.. which can be irritating down the line

A fair point... sort of - after all you can have your database query other databases too.

In summary, for the scope of a project of this size, you could very well get a reasonable solution without writing an API. However I've just seen it so many times where the PM/client says "oh its just a small thing, get it done quick/dirty" and then later on come back and say "Oh right, it worked great we want to roll it out to all of Europe".

On this point I competently agree. Cutting out middleware is a nice idea, but better geared twoards an SME with fewer than 20 users beyond that and I suspect you'll start having problems scaling.

It's also something that might work as long as you don't really want to 'future proof' it too much; that is, it starts as a small thing and stays that way.

Returning to the OP, what I'm thinking is that if the requirement is for an app, freely downloadable, that hooks into an online database and allows you to use interfaces to interact with it as a user (not DBA), geared twoards a personal or SME market, then it's not a bad way to go about it.

Of course, we still have to discuss a way to define those interfaces, with such an app.

ChRoMe

The Corinthian wrote: »

It's not like it's getting anything different to what it would get if you were returning the data as XML or whatever. The actual 'clever stuff' is happening in the stored procedure in the database - the client is just rendering the same data in the same way as if it had received it from middleware.

Who says that all the clients have to log in using the same user creds?

A fair point... sort of - after all you can have your database query other databases too.

On this point I competently agree. Cutting out middleware is a nice idea, but better geared twoards an SME with fewer than 20 users beyond that and I suspect you'll start having problems scaling.

It's also something that might work as long as you don't really want to 'future proof' it too much; that is, it starts as a small thing and stays that way.

Returning to the OP, what I'm thinking is that if the requirement is for an app, freely downloadable, that hooks into an online database and allows you to use interfaces to interact with it as a user (not DBA), geared twoards a personal or SME market, then it's not a bad way to go about it.

Of course, we still have to discuss a way to define those interfaces, with such an app.

Well having a separate db user connecting directly, can lead to some interesting situations regarding connection pooling/load. Not to mention it would be an administration overhead.

Yes, you can have a database query another. However what about other datasources such as webservices and their associated output formats (json,xml etc).

My point is that, the decisions you are advocating are all tightly coupled and would require a pretty sizable overhaul to scale or make any significant changes. Which are all just generally bad design decisions. Its always a better idea to just build it the correct way from the start. A big part of development is seeing the bigger picture and designing defnesively against your requirements. The old henry ford quote comes to mind "If I had asked my customers what they wanted, they would have told me faster horses".

The Corinthian

ChRoMe wrote: »

My point is that, the decisions you are advocating are all tightly coupled and would require a pretty sizable overhaul to scale or make any significant changes. Which are all just generally bad design decisions. Its always a better idea to just build it the correct way from the start. A big part of development is seeing the bigger picture and designing defnesively against your requirements. The old henry ford quote comes to mind "If I had asked my customers what they wanted, they would have told me faster horses".

I completely agree, but as we said, this isn't exactly a serious discussion - certainly I would never suggest such a solution to a client, simply because I know what a clusterfùck it would end up becoming in the long term.

However, it might make for an interesting approach for someone's limited requirements, like the OP.

ChRoMe

The Corinthian wrote: »

I completely agree, but as we said, this isn't exactly a serious discussion - certainly I would never suggest such a solution to a client, simply because I know what a clusterfùck it would end up becoming in the long term.

However, it might make for an interesting approach for someone's limited requirements, like the OP.

Fair enough, I know to an extent I'm preaching to the converted... however at the same time I don't understand why if you don't consider it acceptable for a client, why you would consider it acceptable as advice for the OP.

The Corinthian

ChRoMe wrote: »

Fair enough, I know to an extent I'm preaching to the converted... however at the same time I don't understand why if you don't consider it acceptable for a client, why you would consider it acceptable as advice for the OP.

It's not really advice for the OP though - my advice to him was really just in my first post. The rest has been semi-OT.

Let's put it this way, the OP originally came here to see if such an app already out there - he does not seem terribly interested in a bespoke development.

Had it existed, it would not have been something that would have been 'exactly' what he wants or that he can later get he developer to extend, as he might were he a client, but a 'close enough' solution that does pretty much what he needs and if in the future he needs something more, he'll have to go out and find a new solution.

I was prompted to muse on this largely because no such app does exist, from what we can see, and was surprised at this as I would imagine that there probably would be some demand for one - I'm sure there's no shortage of small scale users, with limited technical knowledge and/or time, out there who might want the ability to access a remote database with an app.

So if you were to build such an app, you'd want to make it as simple to integrate as possible, and middleware would create an extra level of complexity that would go against this end, so minimizing any integration to the database alone (which one would presume they have resources to deal with) is one way of doing this.

It would never be a scalable app, open to extensive customization, future proofed or 'perfect' by any means, but the user would ultimately get what they paid for.

RainyDay

Thanks for all the feedback. Some of the architectural stuff is a bit over my head, tbh, but I think I get the general concepts.

Graham wrote: »

Without knowing much about the project you have in mind, Filemaker Pro may be an option for you.

Thanks, that's an interesting option. Ideally, I'd like a front-end that would run on Android as well as iPhone, but for the time being, Filemaker might fill a gap. Nice to see that it runs on Windows, and that there is a 30 day free trial available.

Do you happen to know if it supports a 'central database' with multiple users model? I want more than just moving a single database onto a mobile platform. I want multiple mobile users to be able to update a single central DB.

The Corinthian wrote: »

Let's put it this way, the OP originally came here to see if such an app already out there - he does not seem terribly interested in a bespoke development.

Just to clarify - it's not so much that I'm not interested in a bespoke job, I just have no budget for a bespoke job at this stage. I want to prove a concept of mobile apps in a new arena by proving that it can work, and maybe getting a small number of users on board.

If there any other ways that I can achieve this goal, I'm open to suggestions.

The Corinthian wrote: »

It would never be a scalable app, open to extensive customization, future proofed or 'perfect' by any means, but the user would ultimately get what they paid for.

This would be a very reasonable outcome for me at this stage. I'm not too bothered as to whether the short-term solution is scalable. I understand that a good designer would tend to build in scalability from the outset. I'd be happy to accept the limitations on scalability to get to proof-of-concept stage.

The Corinthian

RainyDay wrote: »

Just to clarify - it's not so much that I'm not interested in a bespoke job, I just have no budget for a bespoke job at this stage. I want to prove a concept of mobile apps in a new arena by proving that it can work, and maybe getting a small number of users on board.

If there any other ways that I can achieve this goal, I'm open to suggestions.

If your principle aim is a proof of concept that may lead to a budget later, I think your best bet would be a Web based solution, optimized for mobile and made to look like an app.

MobiOne is not a bad WYSIWYG to produce the front-end, but you'll still have to hack that front end to connect to the database unless you are happy with a static 'smoke-and-mirrors' solution that simply gives the user a feel for how such an app would work.

An actual working solution, that does interact with the database would however still need to be developed as a bespoke piece of work, but this is ultimately bog standard Web development, with a mobile façade, and thus easier to develop yourself or outsource.

I used the 'smoke-and-mirrors' approach for a client some time back to secure funding for them, and it worked a treat.

This would be a very reasonable outcome for me at this stage. I'm not too bothered as to whether the short-term solution is scalable. I understand that a good designer would tend to build in scalability from the outset. I'd be happy to accept the limitations on scalability to get to proof-of-concept stage.

Unfortunately though, I don't think anyone's published such an app. Of course, I didn't exactly spend hours searching to confirm this, so perhaps if you trawl through Google Play or iTunes long enough, you may get lucky.