I am extremely new to database design, so please excuse my ignorance if I ask things that I should know.
I work in visual effects in the film business. We track shots in a show using a database (filemaker pro, at the moment). The database I put together in FM5 has served us well for a number of years, but it's time for us to grow up and take the system to the next level. I am now searching for the next step, and wonder if mySQL is the right platform to migrate to.
Essentially, our database needs fairly simple: tracking a set of about 100 different field types, generating sorts, queries and reports, and exporting data to our vendors. We need to access the information from a variety of forms. We need the database to work locally on a laptop, on set, and then sync to the server upon connection to the internet. I'd like to have some palm/tablet support for gathering information in the field and then synchronizing the information back to the server.
I think I would like the local implementations of the database to be an executable program, which has full access to data, but then syncs when it has a live connection to the server. I need to be able to program a custom front end to the program, and need the underlying database program to be completely transparent -- I want my program to look like I want it to, not like a tarted up database (ie: Filemaker always looks like Filemaker, and it's not good enough for the people who will be using the database).
I'd like the system to be extendable -- ie: once I've developed the database and app for our department, I'd like to be able to port the system to other departments in the film industry, with a central server to compile all information from each department's databases.
I want to take advantage of system software on each user's computer -- at the moment, we are 95% Mac, so I want to be able to tie into quicktime, mail programs, system browsers, address books, photoshop, etc, already installed on each users system.
I'm just in the early stages of putting together a design package for the application, but want to begin researching and learning about the database engine that will fit the needs of the project. At the moment, funds don't allow me to go out of house and hire a database designer to do a proper job of it, but if I can cobble together a working prototype, I might be able to raise so development money to continue the project.
Sorry -- long post: I guess the main question is: will mySQL work locally and sync with a server, or is it a server based system only? And if it is, can you suggest other systems I might look into that are friendly to the beginning/intermediate database designer?