Sql-server – Is this schema usable for multi-tenant clients?How to remove multi-cascade paths

database-designmulti-tenantsql server

I am newbie to database design. I want to build multi-tenant application that will have the following schema for its data:

School(*schoolid*, schoolname, schooltype);

Department(*deptid*,deptname,schoolid);  //schoolid is foreign key

Staff(*staffid*,staffname,contactno,age,courseid, schoolid);  //schoolid,courseid are foreign keys

Course(*courseid*,coursename,coursetype,schoolid);  //schoolid is foreign key

DSC(deptid,staffid,courseid,six,nine); //six,nine are months indicating duration 
of the course to which courseid referes.All ids are foreign keys

SD(staffid,deptid,ten,two,three); //ten,two and three are times at which 
particular staffid is free.All ids are foreign keys

The fields within asterisks(*) are primary fields. I want to have
schoolid in School, Department, Staff, Course tables for security concerns and can't remove it from there. What can be more optimized structure for multi-tenacy in this regard? When I use SQL Server 2012 to build this schema, it *gives multi-cascade cycles error. *.
Any suggestions?

Best Answer

To achieve multi-tennancy you should limit the number of relationships you have to the factor that determines the tennant.

For example, each of Course, Department and Staff have a foreign key to School. However, these child tables are also interrelated. Is it necessary for Staff to have a schoolid? You can get to the school a staff member works for by following the foreign key to Course.

Similarly, you have grand-child tables (SD and DSC) which are relating tables in curious ways. What has "months indicating duration of the course" to do with which staff member is teaching the course? Is it really necessary for DSC to relate to Staff? Also, you appear to have repeating groups defined in your SD and SDC tables. I believe you may have a normalization issue.

By over-relating tables you create cascade cycles. Consider the following ERD as an alternative:

ERD

Here there is only one potential cycle to be concerned with, instead of the several that are in your current model. Fewer potential cycles means less extra work that might need to be done with triggers instead of declarative referential constraints. This keeps your system simpler and more maintainable.

Related Question