ERD for class signups

[blissland]

I run a school and keep track of everything school-related in an FMP12 DB on Mac OS 10.11.  I used to be more immersed in FMP, but now I'm a bit rusty with some of the logic.  Last year I kept track of all non-tuition expenses (books, supplies, elective courses) in one table (ItemPurchasers), and that was sufficient for manually tracking what each student owed.  But it didn't allow me to easily see at a glance which students ordered Item X or signed up for Elective Y.  

ItemPurchasers was not a join table since I did not keep a table of Items to select from (hence the manual aspect).  So in order to know how many of book X or elective Y....it seems that I need to make an Items table and turn the ItemPurchasers into a join table.  

So for this step, I'm wondering if it is a better design to have ONE Items table and ONE join table or is it better for books, supplies, and electives to EACH have their own table, each with their own join (shopping cart) tables between them and the Students table?  [I already have all tuition data in it's own separate tables].  This seems to me like it ought to be an easy question, but for some reason I find it difficult to answer, not knowing what pros and cons I may not be aware of.  

Potentially related to this question is whether I should have a Year field [as a related field?] for each elective and book and supply item.  There are very few books, supplies, and electives, and these are all mostly the same items year after year....but their prices change.  It seems inelegant to have a table like this...
Book A 2015 $40
Book A 2016 $45
Book A 2017 $48
just to ensure that the changing price doesn't screw up old records, but it feels safer.

I'm guessing these are easy answers to those of you who do this a lot.  Thanks so much for your input.  

[comment]

4 hours ago, blissland said:

if it is a better design to have ONE Items table and ONE join table or is it better for books, supplies, and electives to EACH have their own table

If you can, keep them all in one table. If you need very different fields for each type, then consider splitting them. But if you only keep track of them for one, common, purpose (being purchased by students), then at least the common fields should be in a single, common table with a one-to-one relationship to the subtype subtables.

 

4 hours ago, blissland said:

these are all mostly the same items year after year....but their prices change.

The current price needs to be looked up into the record in the join table. This way old records do not get "screwed up".

 

4 hours ago, blissland said:

ItemPurchasers was not a join table since I did not keep a table of Items to select from (hence the manual aspect).  So in order to know how many of book X or elective Y....it seems that I need to make an Items table and turn the ItemPurchasers into a join table.  

You could have sorted the ItemPurchasers by item and use a summary field to count them. Provided you have entered the items in a consistent manner.

[blissland]

This is a different question than the one I posted earlier today.  

I run a school.  Each year there is a different class, and each class has it's own unique packages and payment plans associated with it.  Each student can only be in one class and can only choose one package and one payment plan.  I have the following tables: Students, Class, Packages, PaymentPlans.  There are no many-to-many relationships between these.

The students table has fields for the PackagesID and the PaymentPlansID.  Currently I have the Packages and PaymentPlan tables relating to the Students Table through those two IDs as well as the Year field, which is present in all 4 tables.  The Class table relates to the Students table only through the Year field.  See attached file for more clarity.

Is this the "right" way to do this?  I don't have any problems with this setup, but it seems odd to me.  It sorta seems like the Packages and PaymentPlan tables should relate to the Students table THROUGH the Class table via the Year field in both directions.  But if I did that, it would create a loop.  Should there be another table in between that just contains StudentID, PackageID, and PaymentPlanID?  

I'm sure anyone looking at this will tell me that I need more TOs of the Student table, but leaving that issue aside, is there a 'better' way to relate these 4 tables?

Thank you.

PCAB.pdf

[blissland]

That was very helpful.  Thank you.  

Is there a downside to splitting them into two (cuz that's what I ended up doing tonight).  If there's a downside I'll redo it.

I've never used the Lookup before, and I've had a hard time grasping which to use, even after reading many threads on it.

Regarding the summary field, I understand the concept but not the implementation.  Does it require a script or some extra TO relationships?   I have used basic summary fields, but how would I use it to select only certain items?  I think I'm missing one key concept to grasp how it's done.  

Edited November 21, 2015 by blissland

[comment]

38 minutes ago, blissland said:

Is there a downside to splitting them into two

Any unnecessary complication is a downside. And if you have two join tables, how will you produce a report that summarizes both?

 

38 minutes ago, blissland said:

I've never used the Lookup before,

The classic case of using a lookup is in an invoicing solution - see a demo here: http://fmforums.com/topic/63425-auto-fill-one-field-with-text-from-two-fields/#comment-300150

Your case is practically the same, except that - IIUC - you would have Students instead of Invoices, and no Customers table.

 

38 minutes ago, blissland said:

Regarding the summary field, I understand the concept but not the implementation.

See if the attached helps:

 

 

summary-report.fp7

Edited November 21, 2015 by comment

[Wim Decorte]

The question to answer is:

Can a student have only one or more than one package or payment plan per school year?  If the answer is 1, the the IDs belong in the year table.  If more than one then you need a new join table between the year table (which is already a a join between student and school years) and a new table named someting like "payment_plans_per_year"

[blissland]

a student can only have one package and one payment plan per year.  There is no Year table.  There is a class table, but it doesn't have a StudentID field in it since it's about class-related data that is true across all students.  Did you mean the Class table or did you mean I should create a Year table?  

[Wim Decorte]

You should have a years table, with one record for each student for the school year.  Anything particular about that student for that school year (like the payment plan) should be linked to that, not to the student directly.

[blissland]

OK, I get it.  The attached file shows how I think that would be incorporated.  If I were to put all the workshops and books and supplies in one table, I could also put the TuititionPackages and the PaymentPlans in that same table as well, where each tuition package and each payment plan is an item to purchase.  I don't think it should matter that the latter two has a one-to-one relationship with students while workshop/books/supplies can have a one-to-many.  they'll just be one item in the table for packages and one item for payment plans.  I would also have to move all the fields in the Students table that currently calculate what the student owes--that would all need to move.  Is there a recommended method for making these structural changes when there's already a bunch of data in the database that can easily get lost/broken in the process?  

 

PCAB2.pdf

[comment]

First, you should not use the relationships graph to plan your tables and their relationships. That's what an ERD is for. Based on what you described so far, it should look something like this:

 Note that Students are not directly linked with either PaymentPlans or TuitionPackages. Assuming a Student can enroll in more than Class (in different years), the choice of a Payment Plan and/or a Tuition Package is an attribute of that year's Enrollment record, and needs to be recorded there.

Note also that to implement this in a relationships graph, you will need multiple occurrence of some tables - most likely Payment Plans and Tuition Packages.

 

Edited November 22, 2015 by comment

[blissland]

Thanks for this.  I think your diagram is accurate for most school programs, though I'm not entirely clear about why classes relates to so many objects.  I think my ideal schematic might be slightly different.  My bad for not describing the situation well enough.  

When I started this database, there was just one 6-month program that students signed up for.  So all I needed to do was track enrollment and payments, and then on the side I kept track of an occasional weekend elective class and some purchases (books, etc).  The Class table was only about the main program, not about electives.  Since there was only one main program (or class), I wasn't combining it with the other purchases (books/electives/etc), and I could get away with putting tuition payment fields into the Students table.  The school still operates similarly to this, but now the 6 month program has some slight variations, so it's easier for me to conceptualize it into the one-to-many format of the other purchases.  

As I've attempted describe this more accurately, I realize that your diagram is forcing me to think about this differently.  I can see how your diagram invites me to combine the main 6-month program and the electives into one table, particularly since one of the tuition packages includes the addition of one elective.  Two things that seem to be a problem with combining them are:

1.  payment plans ONLY apply to tuition packages, not electives, and not any individual class.  Maybe the solution to that is to just have a "none" record under payment plans to associate with a class.  in the diagram, perhaps there's no link from classes to payment plans, and there should be a link from payment plans to tuition packages.

2. it's not clear to me how other purchases (books, etc) would fit with this diagram.

 

[comment]

14 minutes ago, blissland said:

I think your diagram is accurate for most school programs

No, it based purely on what you described as your own situation - or rather on what I understood from your description.

 

18 minutes ago, blissland said:

I'm not entirely clear about why classes relates to so many objects.

Because you said that "each class has it's own unique packages and payment plans associated with it." So there is a one-to-many relationship between Classes and Payment Plans, and another one-to-many relationship between Classes and Tuition Packages.

Then you said that "Each student ... can only choose one package and one payment plan.". That means each of the students enrolled in a class has (at most) one Payment Plan and (at most) one Tuition Package, selected from that class's offerings.  IOW, there is a one-to-many relationship between Payment Plans and Enrollments, and another one-to-many relationship between Tuition Packages and Enrollments.

And that's basically all my ERD shows.

 

[blissland]

I apologize for not describing everything as clearly as I should.  When I said "each class has it's own unique packages" I was using the word 'class' to refer to the 'year', as in "the class of 2015".  All the students in the "class" of 2015 are all enrolled in the same 6-month "class".  I should have said that each YEAR has it's own packages and payment plans (though even that statement might not technically be correct).  There has always been a 1-to-1 relationship between year and class historically, so I confuse them easily.  I'm going to take some time to rethink the logic of this so I can ask better questions.  I appreciate how this discussion has helped me clarify the problem.  

[comment]

23 minutes ago, blissland said:

When I said "each class has it's own unique packages" I was using the word 'class' to refer to the 'year', as in "the class of 2015".

How many classes do you have in the year of 2015? Or, more precisely, how many classes can you have in any one year?

[blissland]

If I am counting electives, then the answer is definitely many (the main one plus a handful of electives).

If I am not counting electives, then I would normally say ONE (the main 6 month class that happens each year).

However, for marketing purposes I created three Tuition Packages.  Package B is the ONE regular 6-month program.  Package A is this one program plus an elective at a discounted rate.  Package C is an auditing option for the occasional student who can't attend every portion of the Package B program.  

I think of this as being ONE program with variations.   However for database purposes, perhaps I should be thinking of it as this:

Package A= Classes 1+2+3
Package B=Classes 1+2
Package C=Classes 1

Package B is THE program that students get diplomas for and have ONE payment plan for, so it's odd to think of it as 2 classes.  But it's one way to accommodate for the occasional Package C situation.

Note that students only pay taxes on the main program, and only the main program has payment plans, so perhaps that detail is important to consider.  

Later on in this database I'm hoping I can auto-generate transcripts, and then this becomes another important aspect to consider because while students enroll in and pay for this ONE program (or class or package), on their transcripts it will be listed as FOUR (sub)classes.  Almost every student who enrolls in our program is automatically taking these 4 (sub)classes.  It's possible, but rare, that a student signs up for just three of those classes.  Package C equates to only a PORTION of a particular one of those subclasses.  It's probably best to design with the subclasses and to think of Package C as it's own separate elective (for which no transcripts will be made) so it doesn't mess up the logic.  In other words, maybe the packages are what get purchased out in front but the subclasses are what get purchased in the back.  I have no idea how to diagram both of those at the same time.

So even without the electives the answer is MANY classes in 2015.  Students purchase different arrangements of those classes.  

[comment]

This sounds like students enroll into programs (packages), not into individual classes. And then you have a many-to-many between Programs and Classes - which may or may not need a join table.

Do students enroll into more than one program - e.g. can a student take Package A in 2015 and continue with Package B in 2016? Or is this a one-time deal?

Edited November 22, 2015 by comment

[blissland]

yes, i think that's it.  so would that look like this:

Students----Enrollments----Packages----PackageClassJoin---Classes

And I think payment plans would just be another "package" that they enroll in (which I guess wouldn't have a class associated with it).

Is that correct or is there a table missing?  In a standard Customers--Invoices--LineItems--Products arrangement, customers is like students, packages is like products, and enrollments is like....?  

Also, given that the "price" field would be in the Packages table. would I just change the price fields as they change or would there be a join table between packages and enrollments that would list the price for each package for each year?  

[comment]

Quote

Also, given that the "price" field would be in the Packages table. would I just change the price fields as they change or would there be a join table between packages and enrollments that would list the price for each package for each year?  

I believe it would be best to change the prices in the Packages table whenever you want, and lookup the current price into a field in Enrollments.

 

Quote

Students----Enrollments----Packages----PackageClassJoin---Classes

As I hinted earlier, I am not sure you need a PackageClassJoin table - what information would you put into it? Also, you didn't answer my question regarding repeating students.

 

25 minutes ago, blissland said:

 In a standard Customers--Invoices--LineItems--Products arrangement, customers is like students, packages is like products, and enrollments is like....?  

I don't think the comparison holds, because an invoice documents a purchase of multiple products, where each product has its own price. Here we have a purchase of a single package. True, the package has multiple components - but the price is determined by the package being purchased.

 

25 minutes ago, blissland said:

And I think payment plans would just be another "package" that they enroll in (which I guess wouldn't have a class associated with it).

That doesn't sound right. If there's no class, what are they "buying"?

 

 

 

Edited November 23, 2015 by comment

[blissland]

Yes, I think students enroll in Packages, not classes.  If I am including electives in this, which I think makes sense to do, then each elective is a class, and it just gets copied across as it's own package so that I'm only selecting packages and not both packages and classes.  

Students currently don't enroll in multiple programs, but if I'm counting electives as their own packages, then they absolutely could enroll in multiple ones.  

If each package is going to include various combos of classes, then I would think a join table would be necessary to specify which classes go into which packages.  

Payment Plans can be packages cuz they are buying a payment plan--different plans have different prices.  While it's true that payment plans only associate certain packages, it can just be another item on their account statement.  If they aren't signing up for something that uses a payment plan, then I simply wouldn't be adding it.  Seems simpler than the way I had it before.  

I see your point about invoices and customers.  I've attached an ERD.  

[blissland]

The attached ERD is updated.  The star objects represent the "many" side of the relationships.  I'm very confused as to how the StudentPackages (Enrollment) relates to StudentPurchases.  Doesn't feel right to me, but no idea what to do about it.  

Edited November 23, 2015 by blissland

[blissland]

The lookup that you showed me here is so easy.  If I go back and convert all of my item-price calculation fields into lookup fields, will it keep the same values that are there right now (all values the calc uses are still the same)?  If the values used in the calculation were not the same, would it keep the same result or would it look it up once more when I make it a lookup field?

The summary field is great.  So simple I'm embarrassed that I didn't know about the Count option.  Thank you.  So, what's the pros or cons of keeping all the purchases in one table (and populating the fields with value lists) versus having the purchases in a join table and adding an Items table to list all of the possible items to purchase?  They seem surprisingly similar in their functionality.

[blissland]

I have a Status field in the table where I specify in what stage of the enrollment process (from first contact to graduating) the person is in.  I also have fields to remind me whether I emailed them or called them or need to call them back.  I filter by the Status field to show me in one layout a list of everyone who has contacted me or in other layouts just those who have applied or are enrolled or have graduated.  But now it occurs to me that filtering by this field is not as effective as having a Person-Class join table for everyone that's enrolled in that class.  This is simple enough, but what isn't clear to me is which data fields go into the Person table and which data fields go into the Person-Class table, or in other words, at which point do I enter someone into the Person-Class table.  Here's a typical sequence of steps people go through:

1. Initial contact via email or phone
2a. Apply 1st step
2b. Apply 2nd step
2c. Apply Last step (includes nonrefundable application fee)
3. Enrollment (with payments, some nonrefundable)
4. Graduated

Some people go through 2c and 3 and then drop out at different points. 

Do I just create this one join table or is there a better strategy?  If just this one do I put all the fields related to steps 1 and 2 in the Person table and everything related to 3 and 4 in the join table?  
 

Edited November 25, 2015 by blissland