Single tenant, multiple application database design?

Question

Most questions about tenancy are centered around multi-tenancy database design issues. I want to know about single tenancy but multiple applications. The software I'm developing allows for a single user to create, from a single code base, multiple applications(I call them "sections"):

• user could create a blog inside domain.com/application-blog1, another blog on domain.com/application-blog2.

I've already decided for a single database for everything But I am undecided whether or not I should use multiple tables for different application instances or the same table, maybe with a "sectionId" field to distinguish between them.

I'm using mysql and myisam tables. Could storing everything inside the same table lead to locking issues in the case of having many application instances running?

What's your experience on the subject?

Answer 1

I don't think people will typically use multiple tables in the same database. If you have multiple instances of the same application, you often have separate databases - typically only if new instances are created administratively, rather than through end-user actions. In this case, you'ld put the name of the database into a configuration file, and have the software connect to the right database.

In your case, I would go for the single-schema single-database approach, using sectionIds. This really is the same as multi-tenancy, perhaps minus the need to do access control.

You will of course have locking across concurrent transactions. However, this should never cause problems, since transactions for different sections won't operate on records in a conflicting manner (except when new sections are created - you'll probably have another table telling you what sections you have).