Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB General Discussion » View Thread |
Messages 1 to 6 of 6 total |
Some data base design questions: EDB and EWB |
Sat, Jul 13 2013 8:18 PM | Permanent Link |
Jeff Cook Aspect Systems Ltd | Hi
I _might_ be stepping into a murky new world. The world of "web apps" (or is it webapps - see I'm that ignorant!). Scenario ====== We will (hopefully) have many customers running this new app. Each customer has their own database with their own data but all with the same database format. Each customer will have one or more users who will connect to the customers own database. Each user will also have to access a common database to validate themselves, find out what software options they have paid for etc. and direct them to the correct customer database. Software environment =============== We anticipate writing the app using EWB, using EWBserver to serve the pages and EDBserver to serve the data. Not thought out yet, but probably have a backup copy of the data in a separate location that automatically replicates. The tricky bit ========= How do we design the database? 1. Separate EDB database for each customer? Does that mean a separate EDBServer for each db. Could have dozens of users hooked up at one time, so maybe that isn't feasible. (Especially if the app is wildly successful and we have hundreds of users!) It would be good for backups as you could presumably revert to backup for just one customer quite easily. 2. One EDB database for all customers. Private tables for each customer - maybe use a CustomerID as a prefix to the table name. Reverting to backup would involve reverting just the tables for the given customer. 3. One EDB for all customers with every record it the database carrying the CustomerID as part of it's Primary Key. Reverting to backup for just one customer would have to be ... horrible. 4. Something much better that I haven't thought of .... I haven't thought this out obviously because, as I type this, I realise there are two types of reverting to backup. The first is recovery of the last good version and the second is going back to "last Friday" before the customer deleted some info accidently - or similar. This all a bit much for a Sunday afternoon! Anyway, any pointers to get me started in the right direction would be appreciated. Cheers Jeff -- Jeff Cook Aspect Systems Ltd www.aspect.co.nz |
Sun, Jul 14 2013 11:11 AM | Permanent Link |
Fernando Dias Team Elevate | Jeff,
I would choose option 1, but not a separate EDB server for each DB. You could start with ONE EDB server, one database for each customer, and then you can add servers as the number of customers grow, lets say each server would manage a set of 50 customers, but this is only an example, it would depend on the observed performance. Also, it would be easy to move an EDB server along with it's set of databases from one computer to another. With this design it would also be very easy to isolate problems in a customer's database without affecting other customers, as well as to do backup and restore operations for single customers. -- Fernando Dias [Team Elevate] |
Sun, Jul 14 2013 5:45 PM | Permanent Link |
Jeff Cook Aspect Systems Ltd | Hi Fernando
"Fernando Dias" <fernandodiasAremovthis.easygate.com.pt> wrote in message news:A8800E36-AF1B-4E1B-84D7-C6EED96D155F@news.elevatesoft.com... > Jeff, > > I would choose option 1, but not a separate EDB server for each DB. > You could start with ONE EDB server, one database for each customer, and > then you can add servers as the number of customers grow, lets say each > server would manage a set of 50 customers, but this is only an example, it > would depend on the observed performance. Also, it would be easy to move > an EDB server along with it's set of databases from one computer to > another. With this design it would also be very easy to isolate problems > in a customer's database without affecting other customers, as well as to > do backup and restore operations for single customers. > > -- > Fernando Dias > [Team Elevate] Thanks for that! I've now read the book (in the right place) and thanks to your input I have a better understanding of how the EDBServer works and how I can set it up for multiple databases. I'm coming from the world of DBISAM3 and an application that only ran one database per PC, either file server or client server. I had no idea that one server could serve multiple databases - obvious when reading the manual with no preconceived ideas and an open mind! Cheers Jeff |
Sun, Jul 14 2013 6:44 PM | Permanent Link |
Barry | Jeff, I tend to agree with you. Keep it simple.
But I fear there is a problem with having a separate database for each customer. Maintenance. He is going to have to have a plan in place when he changes the database schema, he is going to have to make the same changes to all 100+ databases at the same time. Jeff, from your experience, have you ever had to do this on multiple live databases? I suppose he could develop a script that will apply these changes to his list of databases automatically. If the database structure becomes out of date with his updated application (webserver or client app), he's going to have a big problem sorting things out. So he will have to make sure all of the database objects have the correct version numbers etc.. I'm thinking off the top of my head: He is going to need 2 sets of everything: Database server, Database directory (and customer directories below it), and web server. The first set could be called "Live" and the second is "Maintenance". He can replicate all of the "live" databases to a separate set of databases in the "Maintenance" directory. Since each customer has a separate database, he can start changing the databases for the customers that are NOT logged in so the currently connected customers won't be impacted for that long a period. Before he does this he will have to make the database for the not logged in customers, read-only in order to prevent changes to the live database while its schema is being updated. The updated database (maintenance) is of course using an EDBServer on a different port. He then needs to change the web application so new connections for this customer will use the new database server. Probably a central EDB database with a CustDb table that has the columns Cust#, Database Version, EDBServer port, Db Directory will tell the EDB Webserver which database to use when the customer connects. He can then continue to convert over the rest of the databases one at a time and update CustDb table. So the old Live database is archived and the Maintenance database then becomes the Live database. A new Maintenance directory is created and the Live database gets replicated here. (Or he can have a separate replicated database directory and create the Maintenance directory only when he is about to update the schema. He will still need to blow away the old replication directory and start fresh when the maintenance db goes live). I've only scratched the surface here, but these are some of the things he's going to have to implement when dealing with multiple databases. Barry |
Sun, Jul 14 2013 10:26 PM | Permanent Link |
Jeff Cook Aspect Systems Ltd | Hi Barry
<Barry> wrote in message news:AB549D33-0363-4FD0-89FC-135052E410D9@news.elevatesoft.com... > Jeff, I tend to agree with you. Keep it simple. > > But I fear there is a problem with having a separate database for each > customer. Maintenance. > He is going to have to have a plan in place when he changes the database > schema, he is going to have to make the same changes to all 100+ databases > at the same time. > Actually "he" is me! We are planning on hosting separate "databases" for each customer of our new application. > > Jeff, from your experience, have you ever had to do this on multiple live > databases? > Yes but in an arguably more difficult situation. We have 60+ databases with several hundred users but running on separate machines out of our direct control. And yes, we have to handle schema updates to match our EXE versions. > I suppose he could develop a script that will apply these changes to his > list of databases automatically. > If the database structure becomes out of date with his updated application > (webserver or client app), he's going to have a big problem sorting things > out. So he will have to make sure all of the database objects have the > correct version numbers etc.. Taking Fernando's suggestion this should not be a problem. All the databases are under our control so we can apply the updates to our own schedule. If we know the version of the database then we will know which version of our webapp to serve to the user. In fact we will probably always need this ability as I expect we will, as now, have "tame users" who are only too willing to try out a new version (beta testers!) especially if it involves a new feature that they requested. Once we release a new database version, we can work to a simple schedule and for each database:- 1. Is there a schema update waiting to run 2. Is there a user logged on? If yes, then leave this database for next time round. 3. Flag the database to prevent login with message on attempted logins, "Half a mo', we are busy, try again in 5 minutes" 4. Update the schema 5. Change the version number of the application to match the new schema 6. Flag the database to allow login again. > > I'm thinking off the top of my head: > > He is going to need 2 sets of everything: Database server, Database > directory (and customer directories below it), and web server. The first > set could be called "Live" and the second is "Maintenance". > Yes, I can see we need two versions of the database as we are planning to replicate for security purposes. Not sure why you'd need two servers apart from load handling. <snip> One thing that we won't be doing is having difference schemas apart from version to version. The application itself will "one size fits all" albeit with the possibility of different options being available. Thanks for your input Barry. It is appreciated and has helped clarify my thinking. Cheers Jeff |
Mon, Jul 15 2013 4:59 PM | Permanent Link |
Barry | "Jeff Cook" wrote:
>Thanks for your input Barry. It is appreciated and has helped clarify my >thinking. No problem. >Not sure why you'd need two servers apart from load handling. After some rethinking, a single EDBSrv should be able to handle it. BTW, since Tim has made C/S memory tables shareable a couple of months ago, you can throw often used readonly or intermediate tables in memory to save wear and tear on the disk drive and it offers some additional security for session data. Barry |
This web page was last updated on Saturday, May 4, 2024 at 12:54 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |