locked
How to design a school billing system database schema?? RRS feed

  • Question

  • I have to build a school billing system for my client. I want to use sql server 2008 for my database.However I just can't finalize my database schema. There are so many complex issues like Advance,Scholarship,discount etc. I am thinking of creating 13 tables. 12 of which belong to month like tblJanuary,tblFebraury etc for each student and the final one for billing.Am I in the right direction?? Can I have some hints?? 
    Thursday, April 4, 2013 11:31 AM

Answers

  • I have to build a school billing system for my client. I want to use sql server 2008 for my database.However I just can't finalize my database schema. There are so many complex issues like Advance,Scholarship,discount etc. I am thinking of creating 13 tables. 12 of which belong to month like tblJanuary,tblFebraury etc for each student and the final one for billing.Am I in the right direction?? Can I have some hints?? 
      Instead of 12 table for 12 months, you can think for a single table having a Month and Year column. if not then just imagine and think how will you handle after year end.

    Thanks
    Manish

    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    • Marked as answer by Maggie Luo Monday, April 15, 2013 10:20 AM
    Thursday, April 4, 2013 12:43 PM
  • I have to build a school billing system for my client. I want to use sql server 2008 for my database.However I just can't finalize my database schema. There are so many complex issues like Advance,Scholarship,discount etc. I am thinking of creating 13 tables. 12 of which belong to month like tblJanuary,tblFebraury etc for each student and the final one for billing.Am I in the right direction?? Can I have some hints?? 

    A good database design will reflect an understanding of the full set of requirements.  What entities must the model reflect and how do they relate?  Students, courses, materials, etc... must be fully defined with associated business rules.  Only then can you create a representative logical model.  Once the logical model accurately reflects the requirements, then you can create the DDL.

    That said, I can't think of any reason why you would need separate tables for each month and student.  A student is an entity, so you would start with what attributes define 'Student' (StudentID, LastName, FirstName, etc) and model that table.  You also would have 'Courses' (I assume) and would do the same thing.  You would have 'Materials' like books and would need to model that table to define the billable materials.  Once you have the entities, then you need the rules for how they relate.  You bring them together in some form of a cross-reference that will reflect which Students enrolled in which Courses with which Materials with associated dates.  Then you need to produce invoices and presumably track payments received and outstanding balances.

    Frankly, there is little that can be done with the information you provided in your original post.

    • Marked as answer by Maggie Luo Monday, April 15, 2013 10:20 AM
    Thursday, April 4, 2013 2:15 PM

All replies

  • I have to build a school billing system for my client. I want to use sql server 2008 for my database.However I just can't finalize my database schema. There are so many complex issues like Advance,Scholarship,discount etc. I am thinking of creating 13 tables. 12 of which belong to month like tblJanuary,tblFebraury etc for each student and the final one for billing.Am I in the right direction?? Can I have some hints?? 
      Instead of 12 table for 12 months, you can think for a single table having a Month and Year column. if not then just imagine and think how will you handle after year end.

    Thanks
    Manish

    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    • Marked as answer by Maggie Luo Monday, April 15, 2013 10:20 AM
    Thursday, April 4, 2013 12:43 PM
  • I have to build a school billing system for my client. I want to use sql server 2008 for my database.However I just can't finalize my database schema. There are so many complex issues like Advance,Scholarship,discount etc. I am thinking of creating 13 tables. 12 of which belong to month like tblJanuary,tblFebraury etc for each student and the final one for billing.Am I in the right direction?? Can I have some hints?? 

    A good database design will reflect an understanding of the full set of requirements.  What entities must the model reflect and how do they relate?  Students, courses, materials, etc... must be fully defined with associated business rules.  Only then can you create a representative logical model.  Once the logical model accurately reflects the requirements, then you can create the DDL.

    That said, I can't think of any reason why you would need separate tables for each month and student.  A student is an entity, so you would start with what attributes define 'Student' (StudentID, LastName, FirstName, etc) and model that table.  You also would have 'Courses' (I assume) and would do the same thing.  You would have 'Materials' like books and would need to model that table to define the billable materials.  Once you have the entities, then you need the rules for how they relate.  You bring them together in some form of a cross-reference that will reflect which Students enrolled in which Courses with which Materials with associated dates.  Then you need to produce invoices and presumably track payments received and outstanding balances.

    Frankly, there is little that can be done with the information you provided in your original post.

    • Marked as answer by Maggie Luo Monday, April 15, 2013 10:20 AM
    Thursday, April 4, 2013 2:15 PM
  • Try designing it backwards. Start with what you want to get out of the system, and what data you need to get there, and the step before that and so forth. That said, while I agree with the other posters that tables representing each month are almost certainly incorrect, 13 tables isn't a lot. 130 is a lot.

    Rebecca M. Riordan

    Friday, April 5, 2013 11:55 AM