Question Which way to go in design (1 Viewer)

ryetee

Registered User.
Local time
Today, 17:48
Joined
Jul 30, 2013
Messages
952
I'm after a bit of advice.
I took a stand alone system and amended it to meet the new requirements of the user. We'll call him user 1. It's a fairly simple database. The system keeps track of merchandise at various clients, how much they have sold and what stock levels are. It was designed for the use of 1 person in 1 area and works well. The user then took on a salesperson who had the same database set up with local data for his area. We'll call him user 2. Now user 1 doesn't want user 2 to see all of the reports. This is not a problem. User 1 also wanted to 'see' user 2's data. User 1 obviously wanted a cheap and cheerful solution. This was achieved by user 2 emailing the database to user 1. This is then copied to a specific location (using VBS) and user 1's front end effectively looks at 2 back ends (his own data and also user 2's). Queries had to be changed to pick up all the data but all in all this works well. The only problem I have is that it's not exactly scaleable. User 1 told me that he wasn't going to increase his sales force. Up until now he hasn't, but....

He is joining forces with someone running a similar operation. They want to use user 1's exiting software and all of it's functionality. He has 5+ users. He also has a proper office and a network!

So finally here comes the question. What's the easiest thing to do to keep the functionality without rewriting it all?

1. Keep it as it is and get all 5+ users to email their DBs to the main user and copy to specific location. I don't think this is viable. If main user expands with more staff then queries/forms will have to be amended.
2. Get users to email their DBs for the particular area they work in but instead of using this as a separate backend copy (after deleting) all of the areas data back into the 1 database. This may have problems if the key is autonum (which I'm sure it is) although I guess that this could be changed.
3. Stick the backend on the server and access remotely somehow! I've read that this fraught with problems but also read that it's not. If each user can only access his own area's data then there should be no record locking problems.
4. Stick it on the web - but I guess that's a problem to someone (ie me) that has no idea where to start with this.
5. Anything else?

I'm thinking 3 is the way forward. Each user has a laptop and can remote desktop into the server , run his front end on his laptop and update the database on the server.

Any thoughts?
 

Mark_

Longboard on the internet
Local time
Today, 10:48
Joined
Sep 12, 2017
Messages
2,111
From a real world version (back in the 90's under DOS) this was accomplished by each sales person having their own copy of the database.

Each record included a "Site ID".

Records are unique (and referenced) by both their ID and their Site_ID.

For your purposes your "User 1" will also have a site ID. Each user that is remote sends their updated records (tracked by having a date assigned when you export for upload) to the home office. This is then added to the database.

For all of your queries and reports, they will either be system wide or by site. You will need to add some user tracking to make sure you know who is accessing what. Other than your main user all others will only have access to their "Site ID".

Program flow for doing updates:
1) End user hits "Export button"
2) End user's FE will go through existing database looking for records who' "Date Exported" is NULL and update with today's DATE.
3) End user's FE will create duplicates of each table.
4) End user's FE will do an append query for each table to the export table based off of "Date Exported" being today.

End user's submit to home office.

At home office, you read in the exported files and append to your back end.

As each site has a unique SITE ID, you won't have duplicates on SITE ID / ID.

In all, having a more stable system where your remote users connect to the existing system (and records are marked by user) would be a lot more stable and preferable, but I'm guessing there are reasons your client doesn't want other's accessing their system directly.
 

Solo712

Registered User.
Local time
Today, 13:48
Joined
Oct 19, 2012
Messages
828
It's really hard to make a call here without seeing the database and the proposed style of interactions between the offices. If your business model is truly distributed, i.e. almost all transactions happen within the area of local office, then you may be better off keeping the database in the current (distributed) form and periodically consolidate it to get the overall performance, trends, and management decision pointers.

As Mark suggested, you may create "areas" or zones and send not perhaps the whole local database but transactions and updates for a given time period. These would be updating the main database, which in turn would produce reports about the whole enterprise for everyone. But as I said, to make an intelligent decision about how to proceed, we would need to see the database design and who does what to it, when and where.

Best,
Jiri
 

ryetee

Registered User.
Local time
Today, 17:48
Joined
Jul 30, 2013
Messages
952
From a real world version (back in the 90's under DOS) this was accomplished by each sales person having their own copy of the database.

Each record included a "Site ID".

Records are unique (and referenced) by both their ID and their Site_ID.

For your purposes your "User 1" will also have a site ID. Each user that is remote sends their updated records (tracked by having a date assigned when you export for upload) to the home office. This is then added to the database.

For all of your queries and reports, they will either be system wide or by site. You will need to add some user tracking to make sure you know who is accessing what. Other than your main user all others will only have access to their "Site ID".

Program flow for doing updates:
1) End user hits "Export button"
2) End user's FE will go through existing database looking for records who' "Date Exported" is NULL and update with today's DATE.
3) End user's FE will create duplicates of each table.
4) End user's FE will do an append query for each table to the export table based off of "Date Exported" being today.

End user's submit to home office.

At home office, you read in the exported files and append to your back end.

As each site has a unique SITE ID, you won't have duplicates on SITE ID / ID.

In all, having a more stable system where your remote users connect to the existing system (and records are marked by user) would be a lot more stable and preferable, but I'm guessing there are reasons your client doesn't want other's accessing their system directly.

Thanks Mark_. So basically my option 2. I'm beginning to favour this as well. I'll need to amend files to add site ID but I think I'd need to do this with option 3 as well anyway.

Not sure what you mean by the last paragraph though...
 

ryetee

Registered User.
Local time
Today, 17:48
Joined
Jul 30, 2013
Messages
952
It's really hard to make a call here without seeing the database and the proposed style of interactions between the offices. If your business model is truly distributed, i.e. almost all transactions happen within the area of local office, then you may be better off keeping the database in the current (distributed) form and periodically consolidate it to get the overall performance, trends, and management decision pointers.

As Mark suggested, you may create "areas" or zones and send not perhaps the whole local database but transactions and updates for a given time period. These would be updating the main database, which in turn would produce reports about the whole enterprise for everyone. But as I said, to make an intelligent decision about how to proceed, we would need to see the database design and who does what to it, when and where.

Best,
Jiri

Basically each agent has his own data and doesn't care about anyone else. So having the system on his own laptop suits him - speed efficiency etc etc. The boss who can also be an agent wants to see his data and his agents data as well. He doesn't need it real time so as suggested data can be sent periodically and loaded into the master database.

I think this is a valid solution. The down side is that any amendments need to be rolled out to a number of different laptops. Any changes to corporate data (i.e costs/prices) need to be rolled out as well.

So I'm wavering between option 2 (copy data from agent to master) and 3 but instead of having the front end on the laptop have it on the server along with the backend. I need to be able to print remotely to the laptop though.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 13:48
Joined
Feb 19, 2002
Messages
42,971
I would split the app into FE and BE if you haven't already done that. Everyone can use the same FE. The field workers wouldn't have any need of some of the import and reporting functions but it doesn't hurt to have them present and it simplifies your job. If everyone is on the same network, distributing new copies of the FE is a piece of cake. I use a batch file. The user opens the app by clicking on a shortcut on his desktop. The shortcut runs a .bat file located on the LAN. The .bat file copies down the latest version of the FE from the master directory. So every time someone opens the app, they get a new copy of the FE which makes it very easy to distribute changes. If you're not all on the same LAN, it is more difficult since you will need to distribute the FE's using email or something like DropBox and you will need to rely on the user to follow your directions and install the new version when you tell him to. If this has to be your method, you should consider creating a notification process so that the FE has a table that gets updated the first time the FE is opened on a local computer and an email is sent to you confirming that the new version is installed.

The BE will be different for each salesperson. You can create an export process that creates a .csv file that is sent to the manager periodically so he can import the sales data from the other users. To make this flow smoothly, you will have to use a two part PK. Part defines the source (salesperson1, salesperson2, etc) and the sequential ID which cannot be an autonumber or there will be conflicts in the manager's database.

The manager then imports the .csv files and the data gets added to his version of the BE. So all reporting needs to be either by specific salesperson or for all salespeople. It won't matter what the salespeople do since they only have their own data. It only matters when the manager is doing reporting. But you want no manager specific code if you can avoid it so all the FE's can be identical.

This is a fragile setup and prone to error. You will have to add code to the application to force it to take backups since the remote PCs are probably nt set up with Carbonite or anything like that which will automatically copy data.
 

ryetee

Registered User.
Local time
Today, 17:48
Joined
Jul 30, 2013
Messages
952
I would split the app into FE and BE if you haven't already done that. Everyone can use the same FE. The field workers wouldn't have any need of some of the import and reporting functions but it doesn't hurt to have them present and it simplifies your job. If everyone is on the same network, distributing new copies of the FE is a piece of cake. I use a batch file. The user opens the app by clicking on a shortcut on his desktop. The shortcut runs a .bat file located on the LAN. The .bat file copies down the latest version of the FE from the master directory. So every time someone opens the app, they get a new copy of the FE which makes it very easy to distribute changes. If you're not all on the same LAN, it is more difficult since you will need to distribute the FE's using email or something like DropBox and you will need to rely on the user to follow your directions and install the new version when you tell him to. If this has to be your method, you should consider creating a notification process so that the FE has a table that gets updated the first time the FE is opened on a local computer and an email is sent to you confirming that the new version is installed.

The BE will be different for each salesperson. You can create an export process that creates a .csv file that is sent to the manager periodically so he can import the sales data from the other users. To make this flow smoothly, you will have to use a two part PK. Part defines the source (salesperson1, salesperson2, etc) and the sequential ID which cannot be an autonumber or there will be conflicts in the manager's database.

The manager then imports the .csv files and the data gets added to his version of the BE. So all reporting needs to be either by specific salesperson or for all salespeople. It won't matter what the salespeople do since they only have their own data. It only matters when the manager is doing reporting. But you want no manager specific code if you can avoid it so all the FE's can be identical.

This is a fragile setup and prone to error. You will have to add code to the application to force it to take backups since the remote PCs are probably nt set up with Carbonite or anything like that which will automatically copy data.
Yeah BE and FE are split. I've decided to go down the route of keeping front ends and back end on 1 server and getting everyone to remote desktop into the server. Already got the copy in place. I'm keeping one back end and separating data via area/agent id. Manager will be able to see all data but agent can only see his data.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 13:48
Joined
Feb 19, 2002
Messages
42,971
Much better solution as long as you can use RDP
 

Users who are viewing this thread

Top Bottom