Hi all. Apologies in advance for a lengthy and probably technically incorrect post.
I'm a serious newbie. I'm using FileMaker to build a database for a university music department to manage and relate our events, contacts, venues, and resources (inventory). I've taken a few online courses through Lynda and I've got something very basic running but it's really just practice for me at this point. It actually mostly works but as I get down in to details and nuance, some problems are beginning to arise.
Essentially, I would like to create a system for reserving rooms (for classes, concerts, rehearsals, other events), checking out equipment or other inventory, and managing events (reserving rooms+equipment). We have various event types, some are departmental and some are rentals (whom I have to pull quotes and invoices for). I'd like to be able to pull reports based on contacts which show what equipment they have checked out or a history of their events. Down the road I'd also like to integrate one of the many calendaring options offered out there. Things like that.
I know that one of the most important things to get right in the initial stages is to get the design/relationships right. This is where I get caught up - I've tried a few different things. For now, I have 5 tables: People, Rooms, Reservations, ReservationLineItems (a join table), Resources. I don't even know if I've identified the appropriate tables. I experimented with not having a "Rooms" table and just considering our rooms "resources" but then I realized that many of the resources (physical inventory) should be assigned to a room or venue. This being the case I'd like to be able to relate those resources to the rooms table. Then I can create query that shows resources by room.
In any case, I'm almost positive I have gotten the relationships wrong, especially because I've related 2 tables to the "reservations" table. See below:
"People" includes various contact info, "Reservations" includes fields like reservation date and time and event descriptions, "ReservationLineItems" includes fields mostly for invoicing (price, qty, etc), and "Resources" contains records for inventory items (name, location, resource type, etc).
Part of the logic I get caught up in is that people should be able to reserve rooms without having to reserve a resource and vice-versa.
Any advice you can offer would be immensely helpful. I'm happy to clarify or provide more information. I hope this isn't too confusing.