Thoughts on when to use one database or two?

[Nagasaki]

I took a filemaker class the other week, and I asked the teacher how he decided whether one database was appropriate to store any and all of his information (some related, some not) or several would do the trick. For example, in the relationships chapter of the FileMaker Pro 7 Bible, they have an invoices database that has fields that pull data from a seperate customer database and an inventory database through a relationship. In the book the invoices, customers and inventory are all seperate tables in seperate databases, but what is to say that they wouldn't be better off as seperate tables in the same database?

Anyway, my teacher said that whenever he anticipated having a relationship between tables, he'd automatically put the tables in the same database, rather than creating a database for each table.

Is this a good rule to go by? The philosophy of relationships sometimes drives me more batty than the technical establishment of them.

Edited December 21, 2006 by Guest

[T-Square]

Although I haven't had to cope with these situations, here are a couple of other possibilities to ponder. Keep in mind that I'm just thinking out loud here.

* Will your application will grow exceptionally large in one area or table? Some suggest that separating out the portion that will grow rapidly is advised.

* Will portions of the dataset be used by different departments with different needs or developer teams? If one department wants a particular set of functions that only it will use on a general set of data (think billing perhaps), or if different developer teams are working on different feature sets (think modular applications), it might be better to have those feature sets isolated from all the rest.

And of course, you might want to separate the user interface from the actual data (that's called the Separation Model ) so that you can change the interface without reloading client data. But that's a horse of a different color, so to speak.

David

[Genx]

I think there are two main reasons to split the actual data in a database (interface v data won't really count here).

1) Modules -- you may want to reuse certain key functions in different projects e.g. scheduling solutions, basic contact management, account settings that sort of stuff

2) Keeping your non-critical data seperate from your more critical data especially for back-up purposes (really comes into consideration where you're storing files in your database and they aren't filereferences -- the files could be 1 gig in size and your actual data only 20mb... you don't want to be backing up 1020mb a night when you could just be backing up 20..

[Søren Dyhr]

the files could be 1 gig in size and your actual data only 20mb

Eyecandy anyone???

--sd

[Genx]

I wasn't referring to interface, i was talking about storing pdf's or images that might be 1-10mb each at a professional photo studio etc. My interface files don't go past 20mb.

[T-Square]

I wasn't referring to interface, i was talking about storing pdf's or images that might be 1-10mb each at a professional photo studio etc

... in which case, the images would arguably be critical data, and the manager would be wise to be backing up that 1GB database after all!

[Genx]

Yes, but maybe not 7 times a week, maybe more like one time a week... maybe you transfer your data back up's to a remote location but have to pull off the photos locally... It was just an example...