Table prefixing with the database connector

[eluser]cwscribner[/eluser]
Hi all.

I'm working on porting an applcation from procedural to OO code and into CI. The application runs on a single database with several table prefixes that correspond to clients.

e.g.
client1_data
client1_info
client2_data
client2_info
client3_data
client3_info
etc.

How can I modify the connection parameters to take a table prefix instead of a database prefix?

Secondary question: Would it be better to use a database per client method? The data held will be used for data mining in the future and from what I understand, its much harder to do data mining when you could have several hundred databases as opposed to several hundred tables in one database. Any input on this would be great...been struggling with this dilemma for a while.

[eluser]toopay[/eluser]
[quote author="cwscribner" date="1311756923"]How can I modify the connection parameters to take a table prefix instead of a database prefix?[/quote]
Why you need to do that?

[quote author="cwscribner" date="1311756923"]Secondary question: Would it be better to use a database per client method? The data held will be used for data mining in the future and from what I understand, its much harder to do data mining when you could have several hundred databases as opposed to several hundred tables in one database. Any input on this would be great...been struggling with this dilemma for a while.[/quote]
Use one database with 100 tables instead 100 database with each only have one table. The reason is, you would not like your app runs slower because your app connect/disconnect database too often in your database abstraction.

[eluser]cwscribner[/eluser]
Your response to my secondary question kind of answered your response to my first question. If I'm going to store data from multiple clients into a single database, I need to segregate their tables for compliance reasons. The only way I can think of to do that would be table prefixing.

[eluser]toopay[/eluser]
By referencing client, did you mean it is within your database abstraction(like you try separated each user with its own prefix), or client there is represent totally different app/domain?

[eluser]cwscribner[/eluser]
[quote author="toopay" date="1311834910"]By referencing client, did you mean it is within your database abstraction(like you try separated each user with its own prefix), or client there is represent totally different app/domain?[/quote]

Ah, my apologies. By "client" I mean that each one will have their own table prefix, not different application.

[eluser]cwscribner[/eluser]
Bump

[eluser]toopay[/eluser]
[quote author="cwscribner" date="1311756923"]Hi all.
How can I modify the connection parameters to take a table prefix instead of a database prefix?
[/quote]
I was already asked this before, why you need to do that? I even doesn't understand why we need to have a "connection parameters" to connect/interact with a table(s).And prefixing in CI is useful for database. For example, general application ussually have at least two databases, one as main database and the second for unit testing. By doing this, we need two different database config so after we tweak different connection parameter in each model, we have an automatic database switching.

In your case, i didnt see you need something special, as connection parameter. Instead, i recomend you just use one table. So in your case, you (should) have just two tables, client_info and client_data, rather than having each client tables like above. You can write an authentification procedure, and referencing each user with 'id'.
[quote author="cwscribner" date="1311756923"]
Secondary question: Would it be better to use a database per client method? [/quote] NO.

[eluser]cwscribner[/eluser]
I understand that pragmatically, its okay to store records for all application users in a single database. But this application will be handling hospital records so for data separation purposes, there should be a separate set of tables for each client. How do I accomplish table prefixing with CI?

[eluser]jblack199[/eluser]
that would just depend on the way you're going to get the table anyway... when the client is added into the system, thats when the table would be created and the prefix would be off something in their information not client1 but like their first name and maybe the last 4 of a client id or something of the sort...

in which case it would be rather easy to do table prefixing... but it all depends on how the functions itself are going to work....

now instead of a table prefix i'd run off 1 table with 4 colums... id, c_id, data and info

id would be primary and auto_increment.. c_id would be the id of the client itself.. and then data and info would be whatever they would be in your method...

however by doing this, you still get the feel of separate tables but the speed in not having to create tables on the fly and not having to deal with any prefixing making the end result being less of a headache for you the programmer. just based off the way you've described what you're achieving this method would be the one I would use mainly for speed, as well as database cleanliness when it comes time for auditing, debugging or even upgrading.

However, if you are dead set on using table prefixing... you could theoretically do it so that the client is added... then you get the unique_id of the last query (the primary auto_increment field - mysql_insert_id() )and use that # as your prefix... so it'd be like

create table ('client'.$id.'_data) or create table ('client'.$id.'_info'); would would theoretically get you the result you're looking for and would make it easy to determine what tables belong to which client based on their unique id and nothing more than that.