[SOLVED] Of database design, world of warcraft and antipatterns

[simpli]

Hi,

I am designing a commercial level system. I will have several corporate clients. Take for example Client A and Client B. By the nature of the service I'm going to be offering, they are going to be storing company sensitive data on my server. I would not want company A to have access to company B's data. Furthermore the site is gonna be using Ajax.

 

I was thinking of applying the instance concept (as in World of Warcraft instance) where each customer would have an instance on my servers and they could do whatever they want in there without access or knowledge to other customers instances. For this to work however, each customer would need its own database which would basically be their own sandboxes. Does it make sense design-wise or am I overlooking something that would allow me to keep everyone in one database and be secure?

Please don't tell me it's an Antipattern. I checked the antipattern presentation and it's not there so there's yet hope. Thanks for your input,

J-R

[JonnoTheDev]

Why would you need separate databases for each customer. One database will suffice. Each customer will have a primary key record i.e. customerId

 

customers

********

customerId

name

 

Each customer will only view records related to their own customerId. For instance if customers have to login then their customerId is stored in a session. That piece of data is used within the database queries. The code in your application should prevent any information from other customers being displayed for instance if you have to pass parameters in the url that are used to query the database.

[simpli]

I'm just afraid a customer will hack into the system and access another customer's data. I know about protecting about sql injection and I built that into my code but I can't help think what if. Also, what if I forget in one query to put the user key and the whole dataset becomes available to everyone. Isolating each customer in their own databases protects for this kinds of lapses.

JR

[Mchl]

Separate tables for each client is an antipattern:

http://datacharmer.blogspot.com/2009/03/normalization-and-smoking.html

 

Separate databases... not necessarily. This is in fact pretty good way to limit users' access using MySQL's privilege system.

[simpli]

Hi again,

I'm thinking, could I assign an encryption key to each company to further protect their data? (That would be on top of the user name and password) for instance:

Company A

customer 1

customer2

 

Company B

customer 1

customer 2

customer 3

each customers for each company would  have their username and password but additionally they would have to provide a company key and unless they do so all they would see is gibberish. Is this feasible / desirable?

[KevinM1]

I've never been great when it comes to large database-driven apps, so please forgive me if this is dense, but....

 

Why not just have one database and normalize them like normal, then in the middleware tier of the app (PHP), use the prototype pattern to give each user 'their own' db object to play with?

 

So long as the basic structure of each company's db is the same (that is, they all rely on the same tables to do business), you can simply give each user a cloned db object parameterized with their specific info.

 

I actually like the 'give them each their own db' idea, given the security concerns, but if that's not an option I think prototyping may be the way to go.

[Mchl]

Having separate database for each client has this advantage, that if one client uses a lot of resources, you can move him to a separate server easily.

 

(That's apart from security issues mentioned before)

[Daniel0]

Why don't you just use MySQL's views and assign permissions to that?

[Mchl]

Could work as well. I'm now thinking how difficult would it be to make a data dump for one company only.

[simpli]

Nightslyr, your avatar cracks me up. Your post however left me dazzed and confused... as if I was punched by your avatar. I'm not sure normalisation has nothing to do with what I want to achieve. and I have no idea what the prototype pattern is. Care to expand?

 

JR

[KevinM1]

Nightslyr, your avatar cracks me up. Your post however left me dazzed and confused... as if I was punched by your avatar. I'm not sure normalisation has nothing to do with what I want to achieve. and I have no idea what the prototype pattern is. Care to expand?

 

JR

 

Well, the prototype design pattern is a creational pattern in which the object(s) you want to construct provide a copy of themselves for the client to use.

 

Typcially, there will be an object registry which is just an associative store of objects available to the client.  When a request for an object is received by this store, it finds the appropriate object and returns a clone of it to the client.

 

With your system, a user would request their db info.  To facilitate that request, your system could return a clone of a generic database object populated with the correct data based on the user's id, or some other identifier.  Then, they could manipulate this data in a disconnected way by dealing with the database object rather than directly using the db.  Once they decide to save their work or otherwise end their session, you could then run the database object through whatever security checks you want to use before saving everything back in the db.

 

So long as your system doesn't require new tables to be added to the db, it shouldn't be hard to construct an object that mirrors your db's structure.

 

As far as normalization goes, I'm just not sure how you want to set up your tables.  It was something of a stray idea/comment I had.

 

More info on the prototype pattern (read the last paragraph, as it's essentially what my idea is): http://en.wikipedia.org/wiki/Prototype_pattern

[simpli]

Nightslayer, thanks for the clarification. Actually I read a bit about prototyping in the meantime. I am not sure it's going to apply as I'm gonna be doing some AJAX and I'm gonna need to update as the user goes. Plus that sounds like loading a whole database dataset in memory am I right?

Thanks for clarifying. And I will further check prototyping.

JR

[KevinM1]

Plus that sounds like loading a whole database dataset in memory am I right?

 

Yup, that's about right.

 

BTW, it would be trivial to integrate it with AJAX.  I mean, since the user would have to send requests to the server manipulate data anyway, there's no reason why it couldn't be combined with immediate DOM interaction.

 

Once again, though, if the data is as critical as you mentioned, this may not be the best approach from a security standpoint.