100,000's of record seperate database or not?

[digitaljunkie]

Hi I'm after some food for though.

We are getting data from a external supplier that is regionalised i.e..

Euro

Germany

UK Eire

France

Just creating a database from the supplied CSV/XML files supplied populates at the moment around 30-60k'ish records each.

Ranging in smallest 21mb to largest 89mb Database.

This information needs to integrate into an existing solution but will get updated weekly/monthly.

My gut feeling is to contain all this data in the appropriate tables in a Supplier Info Database and create filepath relationships back/forth from the existing solution.

But the MD says it would be better to cram it all into our existing solution.

What are your general thoughts on this?

[Fenton]

My feeling matches yours. But I don't think it makes all that much difference. You'd be putting it all into only 1 other file, right? (I think I'd put it all in one table, not one for each country. How useful is it when it's in different tables?)

Another thing you didn't really clarify was what you mean by "update"? Are all the records deleted first? Or only update-matching, and a few more added? If the latter, the scales tip more towards incorporation in the existing file.

The basic limitation of FileMaker (7-8.5) is:

"Number of records per table: 64 quadrillion total records over life time of file."

That's a lot of records. It would take many years to reach it. But creation/deletion of massive numbers of records increases the need for file maintenance, in my opinion. It would be good to occasionally Save a Copy as Compacted, then use the copy (renamed to original of course). This keeps the file as lean as possible. It's also one way to detect corruption.

Another consideration is portability. If you ever email/upload the core files then adding 100's of MB unnecessarily makes exchanges awkward. As a developer that would be a big consideration for me, as I often swap files with my clients. In-house would be different.

Remember you can place a Table Occurrence of the Supplier file on the Relationship Graph of your core file(s). So, as far as scripts and relationships are concerned it makes little difference that it's a separate file.

Another thing you have to handle if they're separate files is Accounts & Privileges. I'm guessing the Supplier file would have little security. Perhaps it could be set to open (File Options) with create-edit-delete privs, and be done with it. Or less access, and handle scripted operations with [x] Run with full access. Otherwise you'd probably want to script Account creation/deletion, or use External Authentication.

MD?