1

My program is intended for multiple projects (clients) use. I am working w/ PHP and Mysql.

For example, the implementation of the program for a client would include all the tables needed, and a list of users of that client.

Each implementation of the program (for separate and completely different clients) would make use of the same set of tables but separate. For example, there will be a userlist TABLE for client A, and one for client B. But they are not the same table.

Am I going to have a separate database (and as a result, initiate a new set of table for each new DB) for each implementation?

What do I have to lookout for I'm still early on in the programming phase, but would like to prepare for down the road. Still programming for the tables within one DB, but I don't want any surprise down the road. I am currently hosted on a public hosting company.

William Sham
  • 12,849
  • 11
  • 50
  • 67
  • 1
    See also [What are the advantages of using a single database for EACH client?](http://stackoverflow.com/questions/13348/what-are-the-advantages-of-using-a-single-database-for-each-client). – Örjan Jämte Jul 06 '11 at 06:34

2 Answers2

1

You can create one master table where it will store the database name for each client and clients login crediential. Once they login, according to their client ID, you need to select the database. For new client registration, you need to create a copy of the database with their client name prefix.

AjayR
  • 4,169
  • 4
  • 44
  • 78
  • Is this the typical way for such implementation ? – William Sham Jun 01 '11 at 01:48
  • It depends. Normally we store the records with an additional column as ClientID to distinguish from other clients in every table. But the issue is when records increases, the efficiency will be affected. – AjayR Jun 01 '11 at 02:02
0

Definitely go with multi tables. It will save you a lot of time in the future if you need to debug/update/delete a single client. Also, if client want a custom fix for something and you decide to make it, than you can hurt your other client's data. Also, you may be need different charset collation.

RRStoyanov
  • 1,162
  • 1
  • 13
  • 23
  • I do have multiple tables in each DB. But my question is, each client will need the same set of tables. But their data can't be mixed in one table. Eg. users from Client Company A shouldn't appear along w/ users from Client Company B in the same userlist master table in my opinion. How should I implement this – William Sham Jun 01 '11 at 00:45
  • I had in mind different databases, not tables. If you want to use same tables in one database (as your questions), I would go with some prefix before tables. Something like `client1_permissions`, `client1_posts`, `client2_permissions`, `client2_posts` and so on. This will create some nice overview and very clear view, but I don't want to imagine if you have 5 tables per client and you at some point have 1000 clients (5000 tables). – RRStoyanov Jun 01 '11 at 00:52