Sql-server – Database Design Confusion

database-designsql-server-2008

I am making a Phonegap based ERP solution for school in which every school can configure their own school individually and can manage the whole school from that single application, in which a main administrator configures the school,i.e., adds all the subjects, teachers, classes, timetables, result, etc.

The application also has roles for students, teachers, parents, and admins in which the admin can define the permissions that all the different roles have plus can add and delete the permissions as well.

The application manages the attendance, time table, results, profile of all the students and staff as well.

I can think of only two ways of doing the same,

  1. To maintain a column of school_id in every table that i create
    and in all the queries that i make i specify the school_id with it.
  2. To create a different database for every school that registers in the application so that all the school data is not in the same table and the single table is not over populated.

I just cant make out with what design I shall go forward with.

Thanks in advance.

Best Answer

Creating independent databases will create the risk that as data structures change with the application, structural changes (and any associated data migrations) must be propagated to all of the databases. This can get tricky. A school_id in the tables that need it will simplify things in the sense that you only need to maintain one database.

Multiple databases would make more sense if you are going to deploy the application to physically separate clients (such as if the application runs independently on a server in the school), but will make maintenance more difficult.

Another place where it might make sense to have a separate database for each client would be if you know there will be slightly different versions of the data structures for each client. Of course, changes to common structures would still have to be propagated to all variant databases, and then you have to ensure that common patches/upgrades to the application work properly with the variants.

In general, I'd try to keep it all in one multi-tenant database, unless there is a very good reason to split it up.

Related Question