[gua]

Ok guys so i got job offer. To build whole business system for one store. Let me first show u img how it need to look. Keep in mind that clients have similar stuff already in use but it is too bugy and old so they want me to build new system from scratch .

https://ibb.co/Sc4Wfr4

Here is an image.

S1 and S2 represent Store 1 and Store 2.
In S1 and S2 we need to have desktop apps built in Delphi. This app will read Database that is written in one Excel file. That excel file have product id's, categories,names,prices and much more... S1 and S2 have their own copy of database and they are 99% same.

When customer buy something in S1 , worker there first check in local excel ( database )file from delphi app and then edit excel file thru delphi app.

They synchronize local databases in S1 and S2 over DB server. Why they use this aproach:
1.They dont need to have 100% accurate realtime resoults.
2.In case they lose internet or electricity they can update local excel file and when they get power back they can send that file to remote server.
3.They only need once a day to synhronize their local dbs so they do upload and donwload only 1-2 times a day. Ofc they can do it any time but there is no need for that..

As u see in image i would like to display products and product info from the same Excel file that is used to synchronize S1 and S2, so people can browse products online and buy stuff online. When people order stuff online i need to send e mail to S1 and after that workers in S1 will update local excel file in S1 and upload it to DB again.



My questions are is this best way to do it ? I mean i need to do it this way since they want me to do this way but any suggestions is acceptable ..

Can delphi do this ? What is best technology to use on server ? I wrote php/laravel but if u have any other solutions please tell me .. Really any info is welcomed .. After writing this i will start googling but i wanted to hear your opinions first, thanks 👍

EDIT:

Lets try to brake this topic..

What is best way to perform crud operations on local excel file with delphi ? I saw file , it have around 6000 rows filled with products.
Every product have own image , but it is in separate folder and it can be loaded every time when somebody select row
Whats best way to perform search
and most important what is the best way to upload and retrieve Excel file from remote server ?

[gua]

The biggest problem is that i dont know should I create some sort of local database and export it to excel than upload to server or just to open and edit excel files ... i really struggle to find a best way for information to flow

[ERD_85]

I read your post multiple times - but i dont understand it - why do you want a local db running in an excel file - and match that to another DB on a server? In delphi you can use native controls to connect to MS SQL Server, you can also access MS SQL through isapi dll's written in Delphi, and though php.

But the best solution seems to me:
- Create one DB - lets say MySQL/MariaDB
- Create an api in php
- Connect the Delphi app through the php api (simple json requests in http)
- Frontend of the store in a javascript framework - VueJS, React? Angular - and connect to the php api

This way you dont need to sync data between db's - and you only need 1 api for both local application as for web application..

[Edit] I just read you want to keep a local db and just sync once a day - that seems a very bad idea, lets say you have product a1.
product a1 has 5 in stock in db on server, but local already sold 5 product a1. When a user looks online they see 5 in stock - but there is none in stock as the local db is not yet synched.

Pm me if you like to talk..

[Nachfolger]

I read your post multiple times - but i dont understand it - why do you want a local db running in an excel file - and match that to another DB on a server? In delphi you can use native controls to connect to MS SQL Server, you can also access MS SQL through isapi dll's written in Delphi, and though php.

But the best solution seems to me:
- Create one DB - lets say MySQL/MariaDB
- Create an api in php
- Connect the Delphi app through the php api (simple json requests in http)
- Frontend of the store in a javascript framework - VueJS, React? Angular - and connect to the php api

This way you dont need to sync data between db's - and you only need 1 api for both local application as for web application..

[Edit] I just read you want to keep a local db and just sync once a day - that seems a very bad idea, lets say you have product a1.
product a1 has 5 in stock in db on server, but local already sold 5 product a1. When a user looks online they see 5 in stock - but there is none in stock as the local db is not yet synched.

Pm me if you like to talk..

I agree that a single multiple "cached" DBs is a bad idea, especially if it's only synced daily. I think the main reasoning for them opting to use Excel is for a offline redundancy.

OP: This company is attempting to modernize their operations, but using very bad ideas. Ensuring data constancy is going to be extremely complicated, especially for the central database. I wonder if you can approach them with the same concept that the above user recommended.

[gua]

I read your post multiple times - but i dont understand it - why do you want a local db running in an excel file - and match that to another DB on a server? In delphi you can use native controls to connect to MS SQL Server, you can also access MS SQL through isapi dll's written in Delphi, and though php.

But the best solution seems to me:
- Create one DB - lets say MySQL/MariaDB
- Create an api in php
- Connect the Delphi app through the php api (simple json requests in http)
- Frontend of the store in a javascript framework - VueJS, React? Angular - and connect to the php api

This way you dont need to sync data between db's - and you only need 1 api for both local application as for web application..

[Edit] I just read you want to keep a local db and just sync once a day - that seems a very bad idea, lets say you have product a1.
product a1 has 5 in stock in db on server, but local already sold 5 product a1. When a user looks online they see 5 in stock - but there is none in stock as the local db is not yet synched.

Pm me if you like to talk..

Hello thank you for your answer.

Well first i suggested them something like this:

"
- Create one DB - lets say MySQL/MariaDB
- Create an api in php
- Connect the Delphi app through the php api (simple json requests in http)
- Frontend of the store in a javascript framework - VueJS, React? Angular - and connect to the php api
"

They rejected it and told me that they need to ensure that their shop works even if they internet/electric power go OFF.
They told me that sometimes they dont have internet for few hours so in that case they cannot use program.
They have their own electric power system in case power goes off , so their computer and electronic cash register can still operate if new customer enter the store.
+
They used to use Excel and their workers there love Excel..
There are some other reasons also but those few reasons that i wrote are main reasons ..

Let me tell you more about store. They sell some type of electric equipment, people dont usually buy big quantity of items from them... If some guy enters the store and say for example "i want 120 subwoofers" a worker in store can click on button and download fresh synchronized DB and check if they have 120 subwoofers.. So i think you got the point now why they dont want api and stuff.. Also they only have 2 shops in total.

I agree that a single multiple "cached" DBs is a bad idea, especially if it's only synced daily. I think the main reasoning for them opting to use Excel is for a offline redundancy.

OP: This company is attempting to modernize their operations, but using very bad ideas. Ensuring data constancy is going to be extremely complicated, especially for the central database. I wonder if you can approach them with the same concept that the above user recommended.

It would be the best for me to do with api calls but they just reject that idea in start...