[austinjackson]

I am creating a site that will host data for several independent clients. The data are sensitive and should not be viewed by the public or the other clients. I have this mostly worked out, but have a best practices question.

I have a table structure devised that will run the show. My current way of doing things is to make sure that each entry is linked to a client through a column (clientID). When pulling records from the database, the scripts I have created pull only the data for the clientID requesting the data.

Is this right/safe? Or should I replicate the table structure for each client to keep the data "walled off" from each other?

I am uncomfortable having the data for many different clients sharing the same set of tables.

How is this done in the "real world"?

[Arkham_c]

Every client should have their own database. Not just their own tables, but their own database entirely. I assume MySQL can do this -- I know it's trivial in PostreSQL or Oracle.

[philzilla]

by hand:

Code:
CREATE DATABASE new_client;
USE new_client;

CREATE TABLE `info` (
`id` INT NOT NULL AUTO_INCREMENT ,
`blah1 VARCHAR (75 )NOT NULL ,
`blah2` VARCHAR (75 )NOT NULL ,
`blah3` VARCHAR (5 )NOT NULL ,
PRIMARY KEY (`id` )
);

...or just use phpMyAdmin

[bens1901]

I have seen an enterprise solution where one database was used to store the data for many independent groups. This was a software package for HR to maintain salary information for many independent groups within one large organization. Each group could have used their own database (each one had enough data for their own db), but the architecture of the web app. was structured for one interface for everyone, with one primary db.

I'm not saying this is the best solution. Just wanted to mention that this was done by a major company, and the data was highly sensitive.

As long as you clearly seperate the data with business logic, then you could use one database.

I could think of 3 options:

1) use one db, same tables, seperation of data is done with sql statement
2) use one db, multiple tables, a different connection (with table priviledges) for each set of users...so group A would have UserA connection and could only read from tables assigned to group A, and so on for the other groups
3) use multiple databases

good luck.

[austinjackson]

Thanks for the replies.

The reason I was interested in using one database was because of hosting considerations. Does anyone know a good hosting place that will allow multiple MySQL accounts and permission to create them?

Aj

[philzilla]

Originally posted by austinjackson:
Does anyone know a good hosting place that will allow multiple MySQL accounts and permission to create them?

www.phpwebhosting.com