Take the 2-minute tour ×
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.

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?

share|improve this question
1  
Why don't you post your actual DDL? The cascade error can only occur if you are cascading updates. I tend not to do that in my designs, but in your case, if that's a feature you actually want to use, then you cannot cascade in a way which causes ambiguity or cycles. –  Cade Roux Jan 1 '13 at 19:12
    
    
What do you mean by "multi-tenancy" in this context? Partitioning the data by school? –  Jon Seigel Jan 1 '13 at 21:19
    
@JonSeigel: Different schools will be our clients and they will use this shared data. –  Bhushan Firake Jan 2 '13 at 5:39
    
Depending on who your clients are, you'll want to be very careful about data partitioning and security -- in some instances, districts (also known as school boards) require (possibly by law) their data to be completely isolated from everyone else. Sometimes a database boundary is okay, sometimes that's not enough. Again, be very, very careful with this, or you'll end up back at the design phase at a late stage in the project. (FYI: I work in this field.) –  Jon Seigel Jan 2 '13 at 14:54

1 Answer 1

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.

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.