What Database structure would best to achieve my business needs and grow if needed

Hi
I was hoping advice as to which path to go with, to develop a program dashboard. I was originally using Microsoft Access, which I have used in the past as a database program.

I needed to do a variety of things and store information. for example, Customer information, tracking correspondence etc.

While being able to conversed with Microsoft Outlook, Microsoft Excel, drop box, office365 and Microsoft Word. also print reports in PDF, sms, etc.

I have been advised, that I should use Microsoft SQL or MySQL and PHP on a home-made server (computer made into server).

Would this be the right approach for a program that I could continually expand on and l learn? especially as we move from a desktop system. so as to achieve my objectives of my business and have a communicate with an accounting package Xero through API!

Could anyone give me a comment or a lead me as to what I should do, I would also like help in understanding SQL and PHP as a means of using the webpage to access information itself and a few other staff. In which I may like them to work from home.

Thank you for your time and help

Scalable modern web applications can be divided into three main parts.

  • front-end application
  • back-end apis
  • cloud infrastructure

The front-end of modern applications are created using JavaScript. Most frequently using one off the three technologies: Angular, Vue, and React.

The back-end apis provide data to the front-end application. These APIs are typically created in the cloud using .NET, Java, Python or nodejs.

Modern applications are not hosted on physical servers. They will live on virtual environments that can be easily scaled in the cloud.

In my opinion everything you were told about building a modern application using php and mysql is incorrect. PHP is really more of a legacy technology that will stick around because there are so many CMS platforms that rely on it. However, if you building something from the ground up would be be best to use a more modern technology rather than a legacy one like php.

JavaScript is a great choice since it can be used on both the server and client. Furthermore, serverless and stateless applications are really the future. Lambdas running the cloud as APIs that don’t require much to set-up at all. You can just push javascript functions and have them return a result.

Also running mysql at scale is very expensive. NoSQL solutions provide a much more flexibility and cost much less. There are a ton of NoSQL options available out there.

As most of what you wrote is correct I would not advice to use a non-relational database if not needed. For a CMS you normally do not need to store many documents and have many full text search in it. So a relational database would be the better choice.

1 Like

A relational database is one of worst architectural decisions to make for full text search given the number of dedicated search options that exist. Just to name the top two in my book.

I’m currently using bonsai.io as a hosted elastic search service. However, will migrating to open search on aws in the future.

As for NoSQL storing documents (objects) in natural form makes much more sense than distributing them across across a convoluted matrix of tables that become increasingly difficult to maintain at scale.

Serverless / API-less

Not to mention. many of these modern services provide rest APIs and javascript sdks that make it possible to query them directly in the client. This makes it possible to build applications without even needing to build your own server-side API.

CDN Natural Form Storage

I actually have been storing entities as documents on my CDN. When users request entities like a blog page instead of an expensive query to MySQL or some other relational database it is mere request for a simple document backed up by a global distribution system with caching. Completely eliminating the need for building my own API or having a database. The CDN acts as its own database storing documents (objects) in natural form using json.

The AWS s3 client can be used directly in the browser to query for documents. No MySQL or even Mongo required.

https://docs.aws.amazon.com/AWSJavaScriptSDK/v3/latest/clients/client-s3/index.html

A working example of this can be found on the below demo page.

https://dpxmq1mxvsyda.cloudfront.net/formly/kitchensink/v1

The object stored in s3 can be found here.

https://9iac3wy8ej.execute-api.us-east-1.amazonaws.com/panelpage/61b970ab-e2b3-11eb-8ccc-ce1ea6aa6910

This is actually using something called API gateway but s3 can be directly accessed without middle api.

Why are you looking to build this from scratch in the first place? Is there no off-the-shelf package that fits your needs?

the trouble with off the shelf packages that they usually miss a lot features, and become rather price the more you get to cover the areas you need. also i like to change things if i don’t find it do’s my requirements

How often do you need to do a full text search in a CMS?

1 Like

I still like my AJAX Search

And your time is free?

I don’t know what your current expertise is in programming, but it sounds like you’re just starting out. I can tell you building what you want is going to take a long time. Think months, not days. Maybe even a year or 2 years, depending on how complicated all the interactions need to be.

1 Like

That is a bit of a loaded question. However, throughout my career applications have always required or evolved to require more search functionality than relational databases can realistically deliver.

I think it’s a fundamental architectural flaw to not consider database alternatives to traditionally relational databases. Yes, everything can just be stuffed into a relational database. However, that approach has many disadvantages. Especially as an application has to scale and more advanced searching beyond filtering is desired. Also many modern solutions provide rest APIs and allow json to be used as a payload rather than convoluted sql queries.

All new relational databases are able to use JSON columns and even searching in this columns is very fast. Relational databases have advanced also in the last years. They are not that static as they were in the 80‘s :slight_smile:

At the end it is a question of your requirements to the software you develop. If you go the way most new software designers go, you take biggest frameworks and just click your application together. That’s lasting in a very powerful but even very slow and resource hungry solution.

Best examples are applications like SAP, Salesforce etc. This CMS can nearly everything, but everything is slow. Just calling one page containing information takes in general 2-5 seconds which for me is not acceptable.

We see this at the. Moment, because the company I work for is changing its CMS from a very old AS400 application called MUST to Service Max. Both systems hold the same data, but MUST provides it in less then 100ms, while Service Max needs for the same information several seconds.

I don’t like this approach even if I know that is the future I cannot change.

I would hardly call using a framework clicking an application together. There is still a lot of work that always needs to be done putting all the pieces together. It’s more like building a Lego set without any instructions or instructions that have been chewed up by a dog and buried throughout a large farm. If it was easy to use frameworks and not just jump into programming using custom code more newcomers would. However, I often see beginners take the misguided easy approach reinventing a bunch of wheels.

Frameworks and open source packages provide consistency, documentation, robustness, and large ecosystem of resources. These are all things imparitive to maintaining an application long term with changing developers and business goals. Vacuum programming might seem easy in the beginning but has significant costs.

By using a combination of open source packages and cloud solutions it’s possible to take the work of one developer and stretch it out to the work of many. Building a stable platform that can be easily handed to others to work on with proficiencies in the technologies being used.

In terms of stuffing data into json columns yes it can be done. However, that still doesn’t change the convoluted api to work with that data nor the horizontal scaling problem of relational databases. Problems that have been completely eliminated with other storages and cloud solutions.

Lastly, I’ve never been impressed by salesforce products. I think they outsource most of the work to poor countries. It shows on their products and customer service.

Hi
Thanks for your reply,
My experience is very little to none.
what i have been trying to achieve at the moment is to get my ideas from my head to paper format, using word and power point

but i needed to know what direction i should be looking at as i was initially looking at MS Access with both front end an back end which will work in the office an maybe if i go home a link it to office 365 or drop box. although i now think there is dataverse which has thrown me a little.

so to inderstand which way to go an learn while operating my accounting practice is what i was initially trying to achive.

thanks everyone for your constructive comments i appreciate them

I could agree more with your sentiment

I am a beginner, that played around with MS Access and basic HTML and MYSQL at Ui and after, which was 30+ years ago.

I think this have changed a lot but the ability of the software in the cloud is similar to that of the 1990’s when we had 386 computers, with a great cost involved