List:General Discussion« Previous MessageNext Message »
From:Martin Ramsch Date:September 22 1999 4:32pm
Subject:Re: Please give me your advice on multiple databases
View as plain text  
On Tue, 1999-09-21 21:16:55 +0200, Jimmy Posius wrote:
> But what's wisdom: each merchant will have his own products, customers,
> orders and so on. 3 possibilities:
> 1) each merchant gets his own (mysql) database. Thisway 500-2500 (or
> more) databases will be created on a Linux system, all with small
> tables.
> 2) create ONE big database and tabelnames get a prefix (the merchants
> Id), e.g. merchant1_customer, merchant2_customer, merchant1_order etc.
> Thisway there's 1 database with a lot of small tables (> 1000-5000). The
> shopping system will use the prefix when accessing the database
> (select...from $this_merchant.customer where...) etc. 
> 3) create ONE database with customer, orders and products tables. Each
> tuple in a table gets an extra column 'merchantId'. Thisway there's ONE
> database, and few tables which will be large. When accessing the mysql
> database, the shopping system will add an extra where clause (e.g.
> select...from customer where... and merchantId='this_merchant') etc.
> All three are possible since they do own the sourcecode of the
> shopping system.
> But, what's wise regarding speed, maintainability? backups, stability?
> Any ideas? Any parameters missing? Other possibilities?

Security and access rights!
  If each merchant is to maintain his/her own data, you maybe have
  to protect the data through MySQL's priviledge system?
  Then only solutions 1 and 2 are possible, with preference on 1.

  Especially with many INSERT/UPDATE/DELETE, maybe even concurrently
  with many SELECT, the smaller the tables the better.

  Having that many tables (5000) in case 2 could be slow because the
  operating system might become slow when scanning the database
  directory with so many files (5000 x 3 = 15000).
  In this respect, solution 1 is much better.

So I think, possibility 1 pobably will be the best one.

Martin Ramsch <m.ramsch@stripped> <URL: >
PGP KeyID=0xE8EF4F75 FiPr=52 44 5E F3 B0 B1 38 26  E4 EC 80 58 7B 31 3A D7
Please give me your advice on multiple databasesjonp21 Sep
  • Re: Please give me your advice on multiple databasesMartin Ramsch22 Sep
    • Re: Please give me your advice on multiple databasesJames Manning23 Sep
      • Re: Please give me your advice on multiple databasesMartin Ramsch23 Sep