Back To Programming / Scripting / Coding Forum

Several Similar MySQL Database Tables ?

  • Lizzard
  • quirky redhead
  • Silver Member
  • User avatar
  • Joined: Apr 25, 2007
  • Posts: 69
  • Loc: Indiana
  • Status: Offline

Post February 27th, 2009, 1:43 pm

Hi. I wondered what the current line of thinking on this is, I have several databases with the exact same information in them. Say we have a "members" set of information. This information has their name, address, business address, every possible phone number, driver's license number and on and on. Currently the information is repeated in 5 or 6 different databases. (databases not tables within a db)
I was wondering if it would be smarter to have that all in one database and then break the specifics down in smaller dbs. For example. Member info in one database with an ID that designates them uniquely. Now, say that member has a license to sell books in Florida but not Texas. So the Florida database would connect to that person via the unique ID. Then on another db note that the florida member gets a 10% commission on sold books, while the member in Texas gets a 7% commission. Then on another db connected again by the unique ID, the 7% commision earning member is part of the Gold club because he has sold over a million books. The Florida member is part of the silver club because this member has only sold a half a million accounts - and so on and so forth.
Currently the same infos are put in each db. The member info is in a database for commissions. Then it is in another completely different db with the license information. Then another completely different database with the type of books that can be sold. etc, etc.

I can see pros and cons for both. But I am at a total redesign on a legacy because we have grown so much that a single query and submit is taking 13-15 minutes. - really not time efficient now is that? -

So do you know the current thought?
  • Anonymous
  • Bot
  • No Avatar
  • Joined: 25 Feb 2008
  • Posts: ?
  • Loc: Ozzuland
  • Status: Online

Post February 27th, 2009, 1:43 pm

  • devilwood
  • Silver Member
  • Silver Member
  • User avatar
  • Joined: Nov 18, 2007
  • Posts: 429
  • Status: Offline

Post March 2nd, 2009, 11:46 pm

As for optimizing mysql tables, sounds like you're doing it right. That info shouldn't need to be repeated in each database. Just store the unique userid. If not primary key then you may index the unique userid for faster searches. So, yeah you would just want the members id in the commissions, licenses, etc databases.


The second question may be easier to just use php to rank as you hinted that you knew you could do that. Though, the query is getting them in order so the rank is actually the array position of the array $row[] so the number 1 seller would be $row[0] remembering that arrays start at 0.
Code: [ Select ]
 
 
$query = "SELECT SellerName, SUM(sales) FROM sales GROUP BY SellerName ORDER BY SUM(sales) DESC";
$r = mysql_query($query);
$rank = 1;
while ($row = mysql_fetch_array($r)) {
         $sales = $row['SUM(sales)'];        
         $seller = $row['SellerName'];
         $displayit .= "$seller All Time Sales Ranking: #". $rank ."<br>";
         $rank ++;
}
echo $displayit;
 
 
  1.  
  2.  
  3. $query = "SELECT SellerName, SUM(sales) FROM sales GROUP BY SellerName ORDER BY SUM(sales) DESC";
  4. $r = mysql_query($query);
  5. $rank = 1;
  6. while ($row = mysql_fetch_array($r)) {
  7.          $sales = $row['SUM(sales)'];        
  8.          $seller = $row['SellerName'];
  9.          $displayit .= "$seller All Time Sales Ranking: #". $rank ."<br>";
  10.          $rank ++;
  11. }
  12. echo $displayit;
  13.  
  14.  
  • Lizzard
  • quirky redhead
  • Silver Member
  • User avatar
  • Joined: Apr 25, 2007
  • Posts: 69
  • Loc: Indiana
  • Status: Offline

Post March 3rd, 2009, 4:58 am

Thank you. I will keep heading in this direction! I appreciate your help.

Liz

Page 1 of 1

Post Information

  • Total Posts in this topic: 3 posts
  • Users browsing this forum: No registered users and 130 guests
  • You cannot post new topics in this forum
  • You cannot reply to topics in this forum
  • You cannot edit your posts in this forum
  • You cannot delete your posts in this forum
  • You cannot post attachments in this forum
 
 

© 2011 Unmelted, LLC. Ozzu® is a registered trademark of Unmelted, LLC.