2 apps 1 database . will it be the Apocalypse?

[Gortyy]

basicly . im doing a php app for my dad's office that will link a super old and dummy stock / sales manager to our wordpress website . 

 

how i did it ? i connected the php project to the Microsoft SQL database that the  stock / sales manager program uses to store the products to read from it and get products references , stock level and price to always update the 

MYSQL database that the wordpress website uses to store all its products too . well the code is perfectly working but im worried about conflicts since their stock / sales manager program will be using that database while i will also be doing a Select request on it every 1 hour . " they most likely will be doing UPDATE requests on the same database with an other user" . the thing is there is already 3 computers with the stock / sales manager program connected to that same database so im wondering if the 3 of them are not creating a conflict would my php app create one ? 

 

to sum it up , 1 database , at least 2 apps connected to it is there any risks ?

 

[jaslion]

As long as you are ONLY READING DATA. It shouldnt be an issue. Unless you lock the database from any access whilst that happens.

 

Sounds like it is a local server hosting it and 3 clients going to it?

[Gortyy]

5 minutes ago, jaslion said:

As long as you are ONLY READING DATA. It shouldnt be an issue. Unless you lock the database from any access whilst that happens.

 

Sounds like it is a local server hosting it and 3 clients going to it?

@jaslion sir yes sir . the Microsoft SQL Database is local . the php code will be running via the cron like function of Windows to lunch and verify the values / fix them once per hour . and my php code will only be reading . even the user its using is made to be read only . and yes 1 local server running the ERP database and 3 computers with an old sage connected to it . thank you so much for your answer

[jaslion]

1 minute ago, Gortyy said:

sir yes sir . the Microsoft SQL Database is local . the php code will be running via the cron like function of Windows to lunch and verify the values / fix them once per hour . and my php code will only be reading . even the user its using is made to be read only . and yes 1 local server running the ERP database and 3 computers with an old sage connected to it . thank you so much for your answer

Defo test it asking users to do a thingy (that if errors is minor) in the system and force initiate a sync. Then see if explosions.

[Ralphred]

This is what the "database server" use case is designed for; whilst I understand your hesitancy, it's only really warranted for Access/MYSQLite style databases, and any "auditing" you do needs to recognise that you are working on a snapshot, so "auditing adjustments" should be relative and not absolute.

[QuantumRand]

2 hours ago, Gortyy said:

i will also be doing a Select request on it every 1 hour .

I'm not sure I understand. Is this Select query what updates the Wordress databse? If so, then yes, it would get out of sync any time theres an Update in that one hour period after you've done your query. Unless Wordpress can query the original database every time the page is loaded, this sync issue cannot be avoided.

 

MySQL works fine in ecosystems with multiple users accessing and updating data. You can use transactions to make sure something doesn't get written while something else is being written. So when you have a row with a stock value of 5 and two users simultaneously try to reduce it by one, you don't get two updates of 5-1 = 4. Using a write transaction will help enforce that the second user cannot do their update while the first user's is in progress, force the second user to do 4-1=3.

 

This isn't a magic bullet though, you still need to implement the transactions correctly.

[Gortyy]

1 hour ago, Ralphred said:

This is what the "database server" use case is designed for; whilst I understand your hesitancy, it's only really warranted for Access/MYSQLite style databases, and any "auditing" you do needs to recognise that you are working on a snapshot, so "auditing adjustments" should be relative and not absolute.

my man , go slowly on me , im not an expert , what do you mean by so "auditing adjustments" should be relative and not absolute. please ?

[Gortyy]

4 minutes ago, QuantumRand said:

I'm not sure I understand. Is this Select query what updates the Wordress databse? If so, then yes, it would get out of sync any time theres an Update in that one hour period after you've done your query. Unless Wordpress can query the original database every time the page is loaded, this sync issue cannot be avoided.

 

MySQL works fine in ecosystems with multiple users accessing and updating data. You can use transactions to make sure something doesn't get written while something else is being written. So when you have a row with a stock value of 5 and two users simultaneously try to reduce it by one, you don't get two updates of 5-1 = 4. Using a write transaction will help enforce that the second user cannot do their update while the first user's is in progress, force the second user to do 4-1=3.

 

This isn't a magic bullet though, you still need to implement the transactions correctly.

no not updating , i meant that there will be 3 users with an ERP ( program to orgenize all what a company does and have , like products details and quantity ...) and me i will have my php code running once every hour to read with a SELECT query all the products and see if they are the same on the website , if they are not the website database will be updated . so basicaly i will only be reading rows 

[QuantumRand]

5 minutes ago, Gortyy said:

no not updating , i meant that there will be 3 users with an ERP ( program to orgenize all what a company does and have , like products details and quantity ...) and me i will have my php code running once every hour to read with a SELECT query all the products and see if they are the same on the website , if they are not the website database will be updated . so basicaly i will only be reading rows 

It's the ERP software that will be responsible for keeping the Database in sync with itself, so you can disregard that second bit of my post about transactional writes.

 

But your Wordpress site will get out of sync with the data base every time the anyone does an update, and it will stay out of sync until you do your hourly Wordpress query. If it's ok for the Wordpress site to lag by up to an hour, then this is fine.

[Gortyy]

10 minutes ago, QuantumRand said:

It's the ERP software that will be responsible for keeping the Database in sync with itself, so you can disregard that second bit of my post about transactional writes.

 

But your Wordpress site will get out of sync with the data base every time the anyone does an update, and it will stay out of sync until you do your hourly Wordpress query. If it's ok for the Wordpress site to lag by up to an hour, then this is fine.

yea its not a big deal , since we dont rly put the products quantity on it we just put instock or out of stock so its not a big deal to have it updated every one hour , sadly its not like we get 23412423 orders a sec , we only get a few per month