Advanced DB coding?

[TapeGun007]

So presume for a moment, that I have a set of databases named like the following:

 

db_CA_Area1

db_CA_Area2

db_CA_Area3

db_CA_Area4

db_CA_Area5

db_CA_Other

db_Los_Angeles

db_San_Diego

db_San_Francisco

 

 

The developer says they created multiple databases to prevent crashes and such like our competition often has.  Keep in mind, all of these databases in this example are for the State of California, USA.

 

So each database is identical in the layout, tables, field names etc.  It's just that the territory is broken up into multiple databases. All of the highly populated states (Like Texas) are broken up like this into multiple databases similar to this.  I have an admin webpage with a drop down and I can select "California" and it will show me every user in all of the databases above (listing about 20 per page).  Every single user across all of these databases for this state having a unique UserID.

 

In my limited knowledge of mySQL, I am assuming that they are loading each database with a SELECT command into an array and then displaying the information?  Or is there SQL that allows access to each of them to pull information at the same time?  Any links that I can read up would be helpful as I don't know what terms to search for on Google or what this might be called.  I hope my question makes sense to you like it does to me. lol

 

Any help as always is greatly appreciated.

Edited January 19, 2017 by TapeGun007

[requinix]

The developer sounds stupid.

 

Yes, at some point there is a query for the users. Either it happened for all of them when the page first loaded, or each state's users are loaded using AJAX (Javascript with a separate request to the server) when you pick a state.

 

Easy non-dev way to check: load the page, disconnect from the internet/network, and pick a state.

[Jacques1]

Good grief. I've seen many “developers” abuse MySQL in many different ways, but this must be the absolutely worst case. How on earth do you even manage this mess? How do you update the layout? How do you make backups? Not at all, I guess.

 

I think your “developer” has no idea what a database even is. If there's anybody in your company who knows what she's doing, ask for her opinion. Because this looks like a massive fuck up which should be fixed immediately.

[Psycho]

The following are my opinions based on working on a large scale application used to "run the business" for thousands of firms worldwide. I am not a SQL engineer, but in my role I am involved in the discussions/processes regarding the database supporting the application and have some insight into what we have done and why.

 

There are some legitimate reasons for having multiple databases such as scalability and separation of customer data (typically for contractual requirements). But, those decisions (aside from contractual reasons) should only be made after thorough analysis by highly competent SQL engineers to ensure that the DB schema is proper, that there are no inefficient queries, etc. Because, having separate databases creates other challenges. If separate databases are necessary, then there shouldn't be any "co-mingling" of data as you describe in the admin page which required running the same query against all the databases.

 

In our case, the account is determined at login and that is used to "know" what database to use. Our support tools also require the support person to provide the account number for any functions so there is no need to run multiple queries.

 

So, I wouldn't take the developer's word on this. If separate DBs are necessary it should have been something that was legitimately analyzed and should be common knowledge as to why that decision was made.

 

On a separate note: We are looking to move to separate Azure DBs for each account for scalability and greater flexibility. For example, our application requires frequent updates due to the industry and the nature of the application requires several hours to do the updates. With firms that have presence world-wide those outages can be impactful even when we do it in the middle of the night on weekends. By separating the data for each firm, we will have the ability to delay updates for some firms if needed.

[TapeGun007]

Thank you all for your input.  We are a start up company, so I would imagine that nothing is too late, except that our funds are starting to get low and we just want to launch without spending a bunch more $$ on development.  I've coded many mySQL and php pages, but most of what I have coded was certainly catering to a small audience and nothing this large.  I do not claim to be an expert, but more of a "hack of all trades".  I have, no doubt, had some serious questions about our developer for many more reasons beyond this (like complete lack of customer service).  Our hope was to get to version 1.0 (which is very close), fire them and hire someone else after launch.

 

@Psycho, this is a large scale application and will have high server demand (or so we hope).  We plan on having a million users in our local city alone.  We definitely want to scale, not just nation wide, but globally.  Users will be downloading and user our iPhone and Android capable applications all of which connect, of course, to the database(s).  I'm not certain this will change any of the opinions listed above.  Of course, we have been charged for the creation of multiple databases....

 

But in the meantime, I still need to know how to access and pull all of this information out (like doing a query to search for a specific user or updating one field for a user).  Any light shed on this (until such a time when we can fix this mess) would be greatly helpful.

 

I've seen such examples of this on StackOverflow:

 

 

SELECT *
FROM database_2.table_2
JOIN database_1.table_1
ON (database_2.table_2.some_field = database_1.table_1.some_other_field)
WHERE database_1.table_1.data_1 LIKE database_2.table_2.data_2

 

Or

 

You can make multiple calls to mysql_connect(), but if the parameters are the same you need to pass true for the '$new_link' (fourth) parameter, otherwise the same connection is reused. For example:
$dbh1 = mysql_connect($hostname, $username, $password);
$dbh2 = mysql_connect($hostname, $username, $password, true);

mysql_select_db('database1', $dbh1);
mysql_select_db('database2', $dbh2);
Then to query database 1 pass the first link identifier:
mysql_query('select * from tablename', $dbh1);
and for database 2 pass the second:
mysql_query('select * from tablename', $dbh2);
If you do not pass a link identifier then the last connection created is used (in this case the one represented by $dbh2) e.g.:
mysql_query('select * from tablename');

[Psycho]

@Psycho, this is a large scale application and will have high server demand (or so we hope).  We plan on having a million users in our local city alone. 

 

That may be, but have you done load testing to know at what point failures will occur and,just as important, where those failures occur? You may be bottle-necked by your storage infrastructure as opposed to the database. My point was there are some instances where having separate databases makes sense, but trying to fix a problem that hasn't been defined/validated is foolhardy.

 

As to your issue, it is difficult to provide a best approach without really understanding the specific problems to solve. But, I can provide some general ideas that may help. Here is the example of one specific problem you posed

 

I have an admin webpage with a drop down and I can select "California" and it will show me every user in all of the databases above (listing about 20 per page).  Every single user across all of these databases for this state having a unique UserID.

 

You state you plan to have millions of users, yet you want to populate a drop-down of all the users in CA. You may want to rethink that - perhaps a search field. Regardless, a more elegant solution would be to only work against a single database at a time. If the DBs for area1, area2, etc. have more meaningful usage for you, then a solution would be to have a "master" database to list out all of the application databases with meaningful names (San Bernadino County, Central CA, etc.). Then, in your admin utility, select the meaningful name which will then set the DB to use. But, if there is no rhyme or reason to the California "areas" you would be stuck with having to run multiple queries and patching the data together or running a complex (potentially performance impacting) query. You should still probably create a master database that has each of the application databases along with a field for the "group" (i.e. California). Then you can have it programatically query all the relevant databases.

 

You can query the same table from multilple databases in one go using the UNION clause:

SELECT * FROM db_CA_Area1.users
  UNION
SELECT * FROM db_CA_Area2.users
  UNION
SELECT * FROM db_CA_Area3.users
  UNION
SELECT * FROM db_CA_Area4.users
 
 . . . etc.

However, I give no promises that the performance of this with ~10 databases would be acceptable.

Edited January 19, 2017 by Psycho

[Jacques1]

The database layout is the product of plain, old incompetence. It has nothing to do with “large-scale applications” or preventing “crashes” (WTF?).

 

In fact, you should forget about “scale” for now. Realistically, this is an amateur project, and the actual goal is to not fail like so many other projects. You can dream of becoming the next Google later.

 

So there isn't any competent technician on your team whom you can talk to and who has the authority to rectify mistakes? That's worse than I thought. Then I'm afraid it's your job to talk to the developer, tell them that you have serious doubts about the current design, that it's impractical and that you suggest a simpler layout (like, you know, one database). Have some examples ready to demonstrate the problem. If that doesn't help, talk to the person responsible for the developer.

 

That's all you can do. No workaround is big enough to fix this problem on the query level.

[Jacques1]

What baffles me is that you make it sound like the project is almost done, yet there doesn't seem to be any code which actually uses the databases.

[Psycho]

So there isn't any competent technician on your team whom you can talk to and who has the authority to rectify mistakes? That's worse than I thought. Then I'm afraid it's your job to talk to the developer, tell them that you have serious doubts about the current design, that it's impractical and that you suggest a simpler layout (like, you know, one database). Have some examples ready to demonstrate the problem. If that doesn't help, talk to the person responsible for the developer.

 

Agreed Jacques1. They are putting the cart before the horse. Only a competent database engineer and appropriate load testing would be able to determine if such an extreme approach is needed. My reasoning for providing the workarounds is that it is not unheard of that those responsible for making decisions don't make the best decisions. E.g. the manager may not have enough technical knowledge and bases the decisions on the developer's opinion or decides that the cost in dollars and time cannot be consumed right now and it isn't work fixing if it "works" right now. So, the OP may be required to find a way to work with what he has been given.

[TapeGun007]

 

 

You state you plan to have millions of users, yet you want to populate a drop-down of all the users in CA. 

 

No.  I did not say that, or at least wasn't intending to.  I'm no expert, but I'm probably at least a decent coder (or at least let me think I am).   I was stating that the developer created an admin page where I could select the STATE first, then see a page of 20 users at a time with numbers (like to go straight to page 8 for example) and a "next page" link.

 

I already created a search function.  Currently, it only searches one database at a time.  I basically want to search through all the databases in a specific state and pull up a single user at a time to edit/update/change some of their information except of course, their ID.  I was looking for advice on how to accomplish this task really, but probably didn't explain it well enough.  I was trying to give the full background of how it was built so you would understand why I'm trying to accomplish that.  I had no idea I was going to get this kind of a response as I do not understand what to expect on a wide scale database environment.  On this I am simply ignorant, but you guys are helping me realize that more than likely (as I have long suspected) that our developer might have been greedy and is charging us for development that didn't necessarily need to be done.

 

@Jacques1, we have both an Android and iPhone application in beta testing that are nearly complete.  When I said "version 1.0" I meant those applications.  The application accesses the database every time a user opens the application to verify their credentials.  Each time they use the application, there is database activity taking place.  We have a fully built out website in which users can sign up etc, all of which interacts with the database.  Then there is admin side of the website which I am building a better version than the developer, all of which interacts with the database.

 

I really wish I could share more information about what it is we are doing with you Psycho, however, a public forum for discussing our plans isn't the place.  If you would like, I can give you my email in a PM and take it offline.  Any advice, including getting rid of the developer and how to move forward would be a welcome and refreshing thought for sure.  If I could just find a developer than does Android, iOS, and knows Laravel, that could understand our project and what we want to accomplish.... oh man...

 

BTW, I only have my developer that was hired.  No other "experts".  The developer was supposed to be the expert.  Everything has been handled by the developer on the technical side.  I have been the one beta testing the application, the website, and trying to steer the UI to where it's good.  I am the most knowledgeable technical person in my company.  The owner of this company is completely non technical to where he gets frustrated at Outlook because he doesn't know how to use it. LOL.  If he had not hired me, he would've already thrown even more money away and this project would never have succeeded.  I am not boasting, it's just the sad reality of our situation.

[Psycho]

Well, I am of the opinion that a project that only requires one developer must not be that significant since it doesn't need experts in various roles (UI development, vs core code development, vs DB design, etc.). So, while it may be hoped that this will be used by millions of users, it doesn't seem like it is being funded as such. If it becomes wildly successful, then the company can hire new people with the right skill-sets to rebuild it correctly.

 

If the current implementation isn't going to be fixed, then I would suggest using the UNION work around.

[TapeGun007]

Yeah.  At some point we'll hire only internal developers.