2 Replies Latest reply on Jul 8, 2010 4:06 AM by craig_gee

    Schema Advice

    craig_gee

      Title

      Schema Advice

      Post

      I'm fairly new to FM but have a moderate grasp of database design. I'm building a solution for a small business that teaches Kung Fu classes.

      The database needs to track many things including regular weekly classes, one-off events, regular/one-off payments (both in and out), instructor hours, class registers, email lists and a bunch of other stuff.

      I investigated using a calendar plug-in for the class scheduling but our class details are so specific (Kids age range, Instructor count, Lead Instructor, Assistant 1, Assistant 2, Extra instructors, Venue, Weekly repetitions, Termly repetitions and tracking which staff have said they can not work a particular class, instructor pay grade, holiday, sickness, volunteer/paid instructors) that it seemed easier to build a custom solution that uses a date ordered list to staff and organise the classes.

      I'm having trouble with how to organise the schema for the classes and events. I want to be able to provide a layout with a list of all the classes AND one-off events for a particular week, showing which instructors are working that class/event, as a way of replacing a paper rota system.

      If I want to show both regular classes and one-off events in a portal do they have to be stored in one table? I'm guessing yes but with classes and event having different details (fields) this seems inefficient.

      I have attached some rough plans for my schema showing my overall idea (top) and further complications about events (bottom).

      The top shows how members will be enrolled in classes through a join table (Class Attendance) and then instances of each class will be generated in a further table, so specific staffing details can be attached to each one with it's own specific date.

      The bottom shows how the events are actually split into categories (seminars and parties), again each with differing details. Here I have attempted to use a join table between the events and the members (Event Attendance) but I'm not sure exactly how to do it. Can I have an event-id field and populate it with either a party-id OR a seminar-id? Or do I have to have one field for party-id's and one for seminar-id's?

      Ultimately there's more categories than this and I will also need to employ similar techniques to payments where there are regular and one-one instances, but some design advice would be much appreciated.

      Thank you in advance! :)

      Schema_Advice.jpg

        • 1. Re: Schema Advice
          philmodjunk

          I think if you treat parties as a special "class" that meets only once, your database will be easier to set up and use.

          Having extra fields in a table definition that are only used for parties and others only used for seminars isn't really a big deal and you can set up customized "detail view" layouts for each type of event if you need to. You also have the option of linking in special "detail" tables for each type of event if you want to.

          • 2. Re: Schema Advice
            craig_gee

            Thanks PhilModJunk that helps me out. I wondered about putting them into the same table but wasn't sure if that was good design or not.

            Cheers!