Icon View Thread

The following is the text of the current message along with any replies.
Messages 1 to 10 of 12 total
Thread Work with many databases
Tue, Jan 20 2015 2:02 PMPermanent Link

Jose Eduardo Helminsky

HPro Informatica

Hi everyone

I have started a project that will deal with a lot of companies (branches) and I am planning to work with many databases, one for each branch.

db001 until db080 for the beginning and of course it will use 80 folders c:\database\db001 until c:\database\db080

I think this idea will isolate each branch specially to transaction purposes. My doubt. Use just one server for all of them or start one instance of dbsrvr with diferent ports for each database ? The idea with many servers is related a server crash (the application) and I stop/start this server and just one branch will stop working instead of 80.

If I use just one server and need to deal with information from more many database (reports for example) should I connect to all databases and extract what I need or connect/extract/close for each database ?

These 80 branches will grow (not so fast) but if I start with a good aproach then other branches will fit in the same schema.

I would like opinions and/or suggestions about other users

If Tim is reading this I like your suggestion too.

Thanks in advance for any comment. Maybe my idea is stupid
Tue, Jan 20 2015 4:54 PMPermanent Link

Raul

Team Elevate Team Elevate

On 1/20/2015 2:02 PM, Jose Eduardo Helminsky wrote:
> I think this idea will isolate each branch specially to transaction purposes. My doubt. Use just one server for all of them or start one instance of dbsrvr with diferent ports for each database ? The idea with many servers is related a server crash (the application) and I stop/start this server and just one branch will stop working instead of 80.

You will definitely have more isolation but you give up ability to
easily roll-up data. Also keeping track of user logins and such will be
more pain (unless you go with one auth database).

I don't know about requirements enough to really advise so rest is just
1 vs 80 answer.

How likely is the dbsrvr crash or a need to restart it ?

In our case the dbsrvr has been very stable so i personally would rate
the risk of needing to restart dbsrvr quite low.

If you're using triggers or custom functions that would require an
updated dbsrvr.exe then that's a different story but how often would
those updates need to happen ?

Managing 80 dbsrvr instances and dealing with unique network and config
issues for each sounds like a nightmare to me.

I would likely go with some fixed number of shared dbsrvrs (we run 4 but
our usage is different than yours) and have the clients ability to
randomly pick one (so you get load balancing) as well as "fail over" to
next one if connection fails.

Ideally have clients check for list of servers before db connect (simple
http query to known central server(s) would do) so you can centrally
control these and add/remove servers with nothing hard-coded on client-side.

> If I use just one server and need to deal with information from more many database (reports for example) should I connect to all databases and extract what I need or connect/extract/close for each database ?

This is a tough one - depending on how much data you need this might be
slow. Going with single database would definitely be easier in this case.

Another option is to have a central roll-up database with some server
side app that dopes the consolidation on regular basis but this would
depend on design requirements (do they need "live" view or is something
that is from last night or maybe few hours old good enough).

> If Tim is reading this I like your suggestion too.

He'll recommend EDB Smile

Raul
Wed, Jan 21 2015 4:07 AMPermanent Link

Jose Eduardo Helminsky

HPro Informatica

Raul

First of all, thanks for share you experience with that.

I agree with you in your statements. One thing that may be useful is numbering tables according the branch like TableCustomers001, TableCustomers002, and sometimes a shared table TableEmployers000 and a special function to open them and use restricted transaction.

I am still in a "thinking" step of the project and every idea will help me. I like the idea of pool of servers (not one for each branch). I am supposing if the number of branches increase a lot I can put 20 branches (for example) in different servers computer.

One thing is a fact. If I split the information in different folders/databases/servers, harder is to retrieve them togheter.

<<
Tim will recommend EDB Smile
>>

I know that, I am with DBISAM since v1.0 but I have around 200 different applications with a lot of libraries I need to convert. I postponed this conversion until (may be) EDB version 3.0 with record lock transaction model. For me this is one of minor DBISAM faulty (by design I know). If DBISAM would have record lock transaction model and search SQL using more fields in the key like EDB does. It will be almost perfect.

Thanks again but holding other opinions from you and another users

Eduardo
Wed, Jan 21 2015 9:02 AMPermanent Link

Raul

Team Elevate Team Elevate

On 1/21/2015 4:07 AM, Jose Eduardo Helminsky wrote:
> I agree with you in your statements. One thing that may be useful is numbering tables according the branch like TableCustomers001, TableCustomers002, and sometimes a shared table TableEmployers000 and a special function to open them and use restricted transaction.

That would work but as i think about it would all branches have their
own list of customers? What if customers needs service from multiple
branches ?

There might be a need to share even more data. Not sure what type of
business this is but simple things like common lookup fields or
something as trivial as person titles usually should be the same.

Single client/customer list tends to be important as well and do they
need all client transactions to be seen by every branch ?

If they do inventory then again do they need to see each others
inventory and/or share common categories or accounting codes or such.

Raul
Wed, Jan 21 2015 9:47 AMPermanent Link

Roy Lambert

NLH Associates

Team Elevate Team Elevate

Jose


You haven't mentioned what you expect the number of users, file sizes, number of transactions, geographical dispersion etc.

If the branches are dispersed then you schema is more valid, especially if you decide to have equipment at each branch (much better than having to run over the internet/vpn)

Corporate level reporting if you go down that route would probably be best approached by each branch producing a set of report tables which are loaded to a central machine.


Roy Lambert
Wed, Jan 21 2015 11:19 AMPermanent Link

Jose Eduardo Helminsky

HPro Informatica

Raul

I think you have gotten the idea. Some tables are specific to each branch and others (products, customers, etc) are shared. But there are situations where I need to get information from all branches and need to open (80 x tables required).

If I put a single field called "branch" on every table the problem we are discussing disapear but the contention when a transaction (even restricted) happen will be the problem. Or can be the problem. That is the reason I am trying to isolate each branch or use different approach.

One main office with around 40 users and other 80 branches with around 5 users, therefore we are dealing with around 500 users. This is a POS system. Now they use one application for each branch from different suppliers and they do not talk each others neigher send data to the main office. The management happens only using reports in paper and with the tools each branch have to improve the purchase or request products from the main office.

BTW, thanks Raul, every suggestion/opinion are valuable.

Eduardo
Wed, Jan 21 2015 11:21 AMPermanent Link

Jose Eduardo Helminsky

HPro Informatica

Roy

Please, see the answer I posted to Raul about users.

The main idea is put the server (or servers) on the main office and they do not know exactly if the cloud could be an alternative to them.

OT: Should I use "main office" or "headquarter" when I refer to a parent-company and their branches ?

Eduardo
Wed, Jan 21 2015 1:00 PMPermanent Link

Matthew Jones

Jose Eduardo Helminsky wrote:

> But there are situations where I need to get information from all
> branches and need to open (80 x tables required).

I've pondered this a little, and my take is this:
The future is uncertain, but you want to make it the way that gives you
the easiest change when it is needed.

* If you start with 80 tables, one for each branch, then it will be
impossible to merge them into a single table later, as it just won't be
practical.

* If you start with a single table with a "BranchID" value, then it
will be easy to add individual tables later.

The key is that you will start off with a system that is sharing the
data, and is designed to be secure to separate the data from each
branch so they cannot see it. You could determine the amount of data
and decide that it is going to get large, so have a mirror table so
that "today" is all in a small table, and every night it is copied to
the "everything" table that is used for reports. That doesn't stop
reporting from today, but keeps them separate for the big, slow
analysis.

Anyway, just some thoughts. I'd go with big and flexible.

--

Matthew Jones
Thu, Jan 22 2015 5:12 AMPermanent Link

Roy Lambert

NLH Associates

Team Elevate Team Elevate

Jose

>Please, see the answer I posted to Raul about users.

Saw that

>The main idea is put the server (or servers) on the main office and they do not know exactly if the cloud could be an alternative to them.
>
>OT: Should I use "main office" or "headquarter" when I refer to a parent-company and their branches ?

Either will do.

Roy Lambert

Thu, Jan 22 2015 5:34 AMPermanent Link

Roy Lambert

NLH Associates

Team Elevate Team Elevate

Jose


If its a POS system they would, in my opinion, be mad to have all the servers in one building. Having a server at each of the 80 branches provides a lot of resilience in case there's a power cut or someone puts a JCB through the comms line.

If you were using ElevateDB it would be a prime candidate for replication.

Having made the statement in the first line I'd also have to say a lot depends on the type of POS system. Retail or trade sales, restaurant or whatever. Are there corporate accounts is there a central product / price list, how active are the POS terminals etc etc.

If nothing else it'll be "fun" designing it Smiley

Roy Lambert
Page 1 of 2Next Page »
Jump to Page:  1 2
Image