Invoice / Item relationship question

[Anuviel]

Hello. It has been a while since I was here - good to be back.

 

I have a following question about an invoice solution.

 

Background: Creating an invoicing solution for myself to keep track and run statistics for English lessons, students and teachers.

 

Before I start the following is not clear to me, I have the following items:

 

Lessons | Students | Teachers | Schools

 

The invoice will be monthly generated, what I am having trouble deciding on is how to relate the above...

 

The Teacher teaches any number of lessons in a month. Each lesson is set in duration and cost so I am thinking that Lessons should be an Item table and the Teacher should be a Customer table. Easy enough to relate the two but I get confused by Students. Should a Student table be another Item table or a Customer table?

 

For example:

 

Month: January     Teacher: X

 

1. LessonA 1hr StudentX 2000

2. LessonB 2hr StudentY 4000

 

Basically the above is what I want to have in my invoice, hope it is clear from the example.

 

I am thinking of doing the following tables and relationships:

 

Lessons_Item | Join_Table | Invoices | Customers | Students

 

Lessons_Item::Item.ID = Join_Table::Item.ID

Join_Table::Invoice.ID = Invoices::Invoice.ID

Invoices::Customer.ID = Customers::Customer.ID

 

I am getting lost as how to or where to insert the Students table into the above relationship, should it be on the invoice side or the customer side...

 

Any help is greatly appreciated.

 

Thanks & Happy New year...

[Anuviel]

Hi, I just went ahead and have added the Students table on the side of Items - seems to be working ok. If I am wrong please let me know.

 

I however ran into a new issue; One lesson can have multiple students or a single student, not sure how to set that up.

Should I set a separate table where I assign students to a lesson so have something like:

 

 

 

Month: January     Teacher: X

 

1. LessonA 1hr StudentX 2000

2. LessonB 2hr StudentY 4000

                         StudentX

                         StudentM

 

Thanks