0

I have a Codeigniter project that follows an MVC pattern and it has worked perfectly with a single database. Our database has multiple entities - applications, users, etc. - not important. Last week, I was presented with a task, that we need a copy of the project, which will have access to our information, but must have a possibility to have its own private information. Another constraint - they must have the ownership of their database.

Variables - Project_1, DB_1, Project_2, DB_2, DB_Shared.

My idea is to create a shared database which will have most of information of DB_1, so Project_1 and Project_2 can access it, and each project will have its own private database, to which the other project will not have access to.

Right now I am in the planning stage - I have never worked with multiple databases, and I think it adds another level of difficulty because from the start I did not plan my project to have multiple databases in the fututre. I am more interested in some tips of how I should tackle the problem, because it seems there are so many solutions, but at the moment, due to the lack of experience, I can't foresee problems that may arise.

Would I have to rewrite all DB calls? Because I have a link system like url/controller/entity/id - id is not longer a unique element, because entities can exist in 2 database. Would I have to write connection fields? Because on pages, where I have to display all data, I would need to merge two responses together and I would need to somehow distinguish the results.

Alexey
  • 3,607
  • 8
  • 34
  • 54
  • It depends on what is intended to be achieved. There can be projects having access to multiple databases. There can be one database referencing some table of the other. There's no general best pattern. – Pinke Helga Feb 14 '19 at 08:40
  • for multiple database, have you read this: https://stackoverflow.com/questions/8268853/codeigniter-multiple-database-connections – Sofyan Thayf Feb 14 '19 at 08:41
  • @SofyanThayf I have, but that is just the connection part. – Alexey Feb 14 '19 at 08:43
  • @Quasimodo'sclone In the first paragraph I specify what is needed to achieve. We need a project copy, and we need so both copies have access to same shared info, but only need to have private info. So 1 private DB per project + 1 shared db – Alexey Feb 14 '19 at 08:44
  • There's also the management of database users, permissions on database, tables, columns... – Pinke Helga Feb 14 '19 at 08:46
  • Ok, why not just copy the db, create a new user giving him access to both? What is the problem? – Pinke Helga Feb 14 '19 at 08:47
  • @Quasimodo'sclone That is exactly why I am asking the question here, so I can eliminate some dark areas in my understanding of this problem. – Alexey Feb 14 '19 at 08:48
  • You can create as many users as you want. Each user can have create, insert, update, select (, ...) privileges on databases, tables aso. You can finetune who may do what. – Pinke Helga Feb 14 '19 at 08:50
  • @Quasimodo'sclone There are hundreds of users. We have around 400 users. 120 of them belong to a certain company and decided that they wanted their own version of our tool. So they want to still use our information, be able to login to both projects because we get money from them creating applicaitons, but they want to part ways at some point and sell stuff that is not relevant to us. – Alexey Feb 14 '19 at 08:50
  • That's what shared hosters do. If you rent a host, you usually get one or more databases and have your own database user. You get administrative rights to your own database only and not even read permissions to other databases of foreign customors. https://dev.mysql.com/doc/refman/8.0/en/grant.html – Pinke Helga Feb 14 '19 at 08:52
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/188400/discussion-between-alexey-and-quasimodos-clone). – Alexey Feb 14 '19 at 08:56

0 Answers0