By Marnix on

Hi,

I have a multi site installation operational and it works like a charm.

The problem I have is that every site will have it's own tables and therefor the amount of tables becomes unmanageble. I know that it is possible to share tables but still there are quite some tables that every site needs to have for it's own.

I intent to use an additional column in the tables instead of the prefix in the table name, this way I don't have additonal tables and the installation becomes much more practical (in my opinion)

To be able to do this I have to re-write the database.inc which looks feaseble to me.

The questions I have are these :

- Why was the table prefix solution chosen?
- Is the additonal column solution considered and if so, why not chosen?
- Is there anybody already doing this?
- does anyone have good arguments to not do this?

I hope someone can help me with this before I spend a lot of time on something somebody already did or somebody had good reasons not to do.

Regards,

Marnix.

⋅ Categories: Drupal 4.7.x

Comments

marcelh’s picture

marcelh commented

This seems like a good way to keep a multi site database manageable... I think Drupal has a pretty robust and solid mechanism for multi sites by design, so I wouldn't be too eager to rewrite any core modules, though, but I am interested in other insights comparing this approach to the standard Drupal one.

Marcel

chueewowee’s picture

chueewowee commented

I'm posting this only to watch this post (could we have 'watch this thread? and email alerts?)

mkabot68’s picture

mkabot68 commented

I should state first that I have only been using Drupal for 5 months. I don't have the answer on how the current multi-site architecture was arrived at.

I am in the process of developing a Drupal install that will support 1,000+ sites off the same code base using the Drupal multi-site architecture. Based on this experience I am VERY happy with the current architecture.

Development
When developing sites, I always work in a "sandbox" version of the site first. When ready, I then package the site and move it to a production server. With a seperate DB or tables this is very easy using phpMyAdmin.

With your suggestion, I would have to fish through each table and pull the appropriate rows. Yes, I could certainly write some tools to do that... but tweaks would be needed each time I add/change/write a new module.

The current architectures supports developing/moving sites with simple SQL tools.

Scaleability
I am starting my Drupal install with a single Dedicated Server. As the site grows, I can easily add a 2nd Dedicated Server - one to handle Drupal/website and one for the DB. As the site continues to grow I can easily split the Druapl part of the sites to multiple Dedicated Servers and even add multiple DB servers if required.

With your suggestion, there seems to be a core assumption of of a single database server - period. This limits scalability at the high end.

Summary
I think your idea would work for simplifying multi-site for a small # of sites. On the other hand, I don't think it would work across the board due to scaleability issues. Then you are into the concept of supporting two methods for multi-site which is probably not a good idea.

Yes, there is some overhead in the duplication of DB tables. Compared to other CMS products this is minimal vs. the duplication of everything (code included). The DB/table concept is easy to understand and deal with using existing tools.

chueewowee’s picture

chueewowee commented

there is an 'autoinstaller' somwhere (4.6 and 4.7) produced by Leki (http://drupal.org/node/33581) that works for multisites on multi databases. SQL access for database creation and prefixing does depend upon the configuration of your Server provider of course. I used it OK, you can add one site at a time from Drupal's engine URL, and it's fast. It includes dumy runs for testing too. I haven't got shared tables to work yet though, but shall try again. My problem there could be my own Server Provider possibly.

Marnix’s picture

Marnix commented

Thank you for the replies, this really helps me in selecting the right solution.

Still have concerns when you use multiple tables, though. If there is an update of a table (like adding a single column) you need to update a table for every domain (in your case a 1000 tables)

Besides that I don't see why the scalability will be restricted to one server, just like when you redirect sites to a second server, I can do that too

Have you thought about updating your tables in case of an update?

Still would like to know why drupal has chosen the table prefix solution.

Regards,

Marnix.

erdemkose’s picture

erdemkose commented

I am not so sure if I understand your question but I will write something.

I think you mean: Use a table column for multi-site installation.

You will have core tables without prefix. for example you user table will look like:

+-----+------+-------+------+
| uid | name | email | site |
+-----+------+-------+------+

Disadvantages:

  1. Tables will be very big and it reduces performance.
  2. If one table is corrupted, every site on this database will be affected.
  3. You have to add site column for every SQL statement and that will keep your database server very busy.
  4. If you want to update only one site, you cannot do it. You have to update every site, which may lead to data loss or data corruption.
  5. You cannot backup a single site. When you import a backup, every site on this database will lose its data even if they were unaffected.

This list can be very long but that is enough.

You SHOULD install Drupal in its own database. If you cannot create a new database, then you should try using database prefix.

--------------------------------------------------------------
http://erdemkose.com/