Replicating a DB and more - long question

[eswanborg]

I have a quite complex DB for tracking all sorts of details of animation production (cartoons) for a studio. This was created for a single production. The DB has about 10 tables, I would guess 120 relationships, about 60 scripts and over 100,000 records. It works beautifully for that one production.

Recently we began work on an additional two productions. Of course the new shows want the same set up. Clearly I can save a clone and set them up with what we have today - however we're constantly tweaking and adjusting things. So if I went the route of a clone, I'd have to make every tweak three times (and if/when more shows come along, who knows how many more times).

So I was thinking that I need to make a single unified front end and can store the data in individual, related DBs. My dilemma is that I can't figure out how that would work.

The interface would need to work the same way, but I can't get my head around how to call just the records from one show at any given time. I know I could use a $$Variable when the user logs in or chooses which production they want to work with - however I can't figure out the relationships then. If I do a layout that pulls records from one of the other DBs my COPY and PASTE functions happening in scripts won't work, because I would have to have the key field (assetID for instance) from each related DB on the layout.

This is by far the most complex DB I've ever created, so making it now branch out to multiple copies is making my head spin a little. I'm using lots of portals and such, so I get the basic ways to show related data - this is just eluding me a bit.

I'm hoping that I'm just having a huge mental block on the easy answer. I know without being able to see it, this doesn't necessarily make a lot of sense, but I'm sure you experienced folks can point me in a better direction.

Thanks in advance for any help you can offer.

For what it's worth - we're hosting this on Windows FMServer Advanced v8.0 and have a conglomeration of Mac & PC users on v8.0 and v8.5 - I develop using Mac FM Advanced v8.5.

[edit] I guess the other piece of this is that I've never felt like I've actually pushed the limits of Filemaker. So if I keep everything in one solution is that a problem? If I triple or quadruple the number of records is that dangerous? Right now it's about 300MB in filesize. I'm just not sure how far to safely go. Having it all in one would certainly make things easier - I could add a $$Variable to check which Production a user has chosen and use that to pull records only from that show - I would have to adjust a lot of scripts, but just once, not 3 or 4 or more times...

Edited October 19, 2007 by Guest

[Fenton]

The solution is to add a Productions table. It would be the "ancestor" of the other tables, added to the top of the chain. It is not much different from adding a child table (kind of the reverse). Right now your "production" is implied, as there is only 1. You need to explicity identify a Production, which would require, at minimum, a table, an auto-enter ID, and a name. The ID would then appear as a foreign key in all the tables which are adjacent, 1 table down, from Productions.

Then you could view the multiple Productions as a simple list. Clicking on one could Go To Related Record [ next table down; Show only related records ]. Or a Form view could show a portal of choices for the next tables down, with Go To Related Record buttons.

This is all basic FileMaker relational design and navigation. If you are setting global variables, Copy/pasting and Finds to get from place to place, you are not really using FileMaker's relational abilities.

As far as whether the number of records would get to be too much, yes, that is something you will have to deal with, eventually. The only real solution then is archiving old records to a separate file. But remember that as time goes by computers get faster. A well-designed database can hold and use a lot of data.

If some of the large size is because of images or files stored in fields, you could consider putting those in their own file.

[eswanborg]

I'll give it another look. I probably just need to step back a little bit and rethink - I've spent so much time building this one, that I'm sure it is an easy answer. I am fine with relationships and how they work. The Copy/Paste is leftover from my early days of scripting - before discovering Set Field and such.

Thanks for the help.

[DataMate]

Dude... you totally want to learn about data separation.. check out our video from the convention on fmp magazine with Matt P. when you've done that, go to http://fmforums.com/forum/showtopic.php?tid/191067/ adn grab my sample... when you get a handle on it, get in touch and I'll help you with your solution.

[eswanborg]

Umm...thanks.... I guess I didn't explain things well. I am fine with relationships and how they work. I'll take a look at your files and see if there's something miraculous that I'm missing. My dilemma at the time was taking an existing DB for a single production and creating three copies of it that all feed into one, and that one has the same interface as the current version. I have a lot of scripting and various tables that need to relate just to their productions. Anyway - I'll take a look at your stuff. I did roll everything into one and have it working fine now - at the time I posted I was hoping someone would have a better solution, but so far I've not seen anything that does what I was looking for (that would've been less work that what I did by putting them all together).

Thanks