Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 0 down vote favorite

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.

share|improve this question
2  
If this is for a real application, I would look closely at existing vendor solutions instead of developing your own. I work in this field right now, and this type of application can get very complicated, particularly when you start factoring in jurisdictional requirements, and how that affects things like what data is recorded, and how the data is reported to administrators. – Jon Seigel 2 days ago
@JonSeigel - yes it is for a real application, I looked for an existing vendor solution but couldn't find any that is a single application and can be customized by differently for each school. – Rahul Jain yesterday
It depends on what you mean by "customized." For a given jurisdiction, the reporting requirements to the central authority are the same across all districts/schools in that jurisdiction. What differs is something like a take-home report card where you might have a different format or logo for each school. Maybe you want a generic custom field to record on a per-school basis. If you don't mind me asking, which jurisdiction are you dealing with here? Are you following a local set of requirements (your location says India), or a standard from somewhere else? – Jon Seigel yesterday
what i mean by customized is that, the UI for the application will remain same, but all the class details and all will be fed into the application by an administrator for the school and the login details for the staff, students and parents will be given out for use after that. The school administrators are the only admins for the system, and will be given the reports and all. – Rahul Jain 19 hours ago
That... isn't what I would call customization. I would go back and re-look at the vendor solutions you already dismissed and take a closer look. Every system I'm aware of that handles multiple schools would have to do exactly what you said. It's not customization; it's a requirement for this type of system. – Jon Seigel 11 hours ago

2 Answers

up vote 2 down vote

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.

share|improve this answer
The existing Data structures would not change and will be same for all the schools, only new data structures will get added when a new module is added in the application, which will also be the same for all the schools, the only reason that I was thinking in maintaining and creating a different database for each school was to keep the data segregated. The application will be deployed on physically separate clients, but with a central server. – Rahul Jain yesterday
up vote 0 down vote
  1. read this:

"Multi-Tenant Data Architecture"

http://msdn.microsoft.com/en-us/library/aa479086.aspx

  1. I would go with one schema per school.

  2. If you used Hibernate (maybe NHibernate) you can automate the DDL generation across schemas:

"Multi-tenancy in Hibernate"

http://docs.jboss.org/hibernate/orm/4.1/devguide/en-US/html/ch16.html

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.