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?