Working on our companies database and wanted some input.
We are an event production company. Lots of Audio, video and lighting equipment with a growing scenic department. The scope of how I would like to build this database is quite vast. Essentially I want to harness the database to the max, and be able to accumulate vasts amounts of data on our business. So currently the relationships go as follows.
There is a master client list. Each client has multiple projects. A project can be spread across multiple venues and on various dates. Some of these venues have multiple rooms, each with their own characteristics; each venue can also have multiple contacts. A project can have multiple estimates and multiple contracts each with their own line item breakdown. Here is where it gets complicated for me.
For every project I would like to assign particular pieces of equipment to particular rooms in the venue, and as I assign the pieces of equipement to the rooms, they populate in a contract in the background. By the time engineering has determined what is needed for the project, the contract is already close to 90% done without really doing much in actually building a contract. The contract line items should be broken down to particular rooms as well as by categories from gear so ie:
While our tech are assigning gear to where they need to be a different record is also being built, which is a pull sheet of what actually gets moved to a truck to go the venue. This pullsheet is handed off the warehouse people who would ideally scan in and out of every piece of equipment based on the pull list. There are multiple other issues that I would like to tackle but let's just stick to this one for now. Any help would be greatly appreciated. A clone of the database has been attached