Designing db for a day camp

[mordecai98]

I am attempting to design a db for a small day camp. It will need to include camper info, staff info, group assignments, tuition calculations and more.



I have made a table with camper vital information, a table with staff information, and a table of groups. If I have campers that are siblings, I'd like to be able to keep track of that as well.

What is the best way to record that information. Also, siblings will get a discount on tuition (5%).

Aside from what I have above, what are the proper ways to relate the groups, siblings, etc.?

I know there are some templates out there, but I feel if I make it from scratch, I'll be able to understand it better, and then more easily create queries and reports, etc.

[June7]

I used to work for a local Camp Fire chapter. We paid a contractor to build a database. Cost about $20,000+. I think they had a basic template they developed for commercial distribution and tailored it for each customer. Every little modification was more cost because they distributed only the run-time version. We couldn't do edits.

In my current job, took 4 years to build and implement a database from scratch (for a laboratory, not camp program).

After considering all your time and related payroll expenses, could be same as paying someone to build it for you. But as you said, you wouldn't understand and be able to modify.

I don't mean to be negative but you need to be aware this won't be a short-term effort. It will take a lot of time, at least months.

There are many ways to structure a db to accomplish what you need. Your questions are too broad in scope to address here. All depends on your business processes and entity relationships.

I suggest you go through some tutorials and learn basics before attempting to build your actual db. Need to understand relational database principles, Access functionality, programming concepts, and macros and/or VBA coding. Here is one place to start http://www.rogersaccesslibrary.com/
and the 'sticky' thread tutorials at http://forums.aspfree.com/microsoft-access-help-18/

Regarding the sibling tracking - I think our db had a Family entity. Guardians and children were somehow associated through Family. I can't be specific about the tables because I never saw the data structure, only guessing because of the way data was entered and queried. It was a very complex db and slow as molasses. The registration data entry somehow kept track of registrations for each Family and determined appropriate fees.

[mordecai98]

I used to work for a local Camp Fire chapter. We paid a contractor to build a database. Cost about $20,000+. I think they had a basic template they developed for commercial distribution and tailored it for each customer. Every little modification was more cost because they distributed only the run-time version. We couldn't do edits.

In my current job, took 4 years to build and implement a database from scratch (for a laboratory, not camp program).

After considering all your time and related payroll expenses, could be same as paying someone to build it for you. But as you said, you wouldn't understand and be able to modify.

I don't mean to be negative but you need to be aware this won't be a short-term effort. It will take a lot of time, at least months.

There are many ways to structure a db to accomplish what you need. Your questions are too broad in scope to address here. All depends on your business processes and entity relationships.

I suggest you go through some tutorials and learn basics before attempting to build your actual db. Need to understand relational database principles, Access functionality, programming concepts, and macros and/or VBA coding. Here is one place to start http://www.rogersaccesslibrary.com/
and the 'sticky' thread tutorials at http://forums.aspfree.com/microsoft-access-help-18/

Regarding the sibling tracking - I think our db had a Family entity. Guardians and children were somehow associated through Family. I can't be specific about the tables because I never saw the data structure, only guessing because of the way data was entered and queried. It was a very complex db and slow as molasses. The registration data entry somehow kept track of registrations for each Family and determined appropriate fees.

I'm not looking at something that complex. This program has about 20-40 campers. I previously had everything in excel, on 4 or 5 tabs. It was difficult to show others how to iput data without messing things up. I simply need help with the basic design, then I can go on from there.

[June7]

You have a start with tables of camper and staff info and groups. What about junction tables that associate campers with groups and staff with groups? What about an accounts table and then a junction table that associates camper(s) with account? Then look at a transactions table for recording invoices and payments. It gets complicated real quick. The more 'user friendly' the more code.

Those tutorials should help you advance the design. Identify your data entities and how they relate. Build tables. I don't know any resources specific to day camp database.