simpli Posted May 8, 2009 Share Posted May 8, 2009 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 Quote Link to comment https://forums.phpfreaks.com/topic/157340-solved-of-database-design-world-of-warcraft-and-antipatterns/ Share on other sites More sharing options...
JonnoTheDev Posted May 8, 2009 Share Posted May 8, 2009 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. Quote Link to comment https://forums.phpfreaks.com/topic/157340-solved-of-database-design-world-of-warcraft-and-antipatterns/#findComment-829308 Share on other sites More sharing options...
simpli Posted May 8, 2009 Author Share Posted May 8, 2009 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 Quote Link to comment https://forums.phpfreaks.com/topic/157340-solved-of-database-design-world-of-warcraft-and-antipatterns/#findComment-829329 Share on other sites More sharing options...
Mchl Posted May 8, 2009 Share Posted May 8, 2009 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. Quote Link to comment https://forums.phpfreaks.com/topic/157340-solved-of-database-design-world-of-warcraft-and-antipatterns/#findComment-829332 Share on other sites More sharing options...
simpli Posted May 8, 2009 Author Share Posted May 8, 2009 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? Quote Link to comment https://forums.phpfreaks.com/topic/157340-solved-of-database-design-world-of-warcraft-and-antipatterns/#findComment-829351 Share on other sites More sharing options...
KevinM1 Posted May 8, 2009 Share Posted May 8, 2009 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. Quote Link to comment https://forums.phpfreaks.com/topic/157340-solved-of-database-design-world-of-warcraft-and-antipatterns/#findComment-829676 Share on other sites More sharing options...
Mchl Posted May 8, 2009 Share Posted May 8, 2009 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) Quote Link to comment https://forums.phpfreaks.com/topic/157340-solved-of-database-design-world-of-warcraft-and-antipatterns/#findComment-829691 Share on other sites More sharing options...
Daniel0 Posted May 8, 2009 Share Posted May 8, 2009 Why don't you just use MySQL's views and assign permissions to that? Quote Link to comment https://forums.phpfreaks.com/topic/157340-solved-of-database-design-world-of-warcraft-and-antipatterns/#findComment-829699 Share on other sites More sharing options...
Mchl Posted May 8, 2009 Share Posted May 8, 2009 Could work as well. I'm now thinking how difficult would it be to make a data dump for one company only. Quote Link to comment https://forums.phpfreaks.com/topic/157340-solved-of-database-design-world-of-warcraft-and-antipatterns/#findComment-829707 Share on other sites More sharing options...
simpli Posted May 8, 2009 Author Share Posted May 8, 2009 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 Quote Link to comment https://forums.phpfreaks.com/topic/157340-solved-of-database-design-world-of-warcraft-and-antipatterns/#findComment-829874 Share on other sites More sharing options...
KevinM1 Posted May 10, 2009 Share Posted May 10, 2009 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 Quote Link to comment https://forums.phpfreaks.com/topic/157340-solved-of-database-design-world-of-warcraft-and-antipatterns/#findComment-831196 Share on other sites More sharing options...
simpli Posted May 11, 2009 Author Share Posted May 11, 2009 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 Quote Link to comment https://forums.phpfreaks.com/topic/157340-solved-of-database-design-world-of-warcraft-and-antipatterns/#findComment-831304 Share on other sites More sharing options...
KevinM1 Posted May 12, 2009 Share Posted May 12, 2009 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. Quote Link to comment https://forums.phpfreaks.com/topic/157340-solved-of-database-design-world-of-warcraft-and-antipatterns/#findComment-832286 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.