what is better?

[sju]

hi i have a dillema what is better. i have three databases and i need to make some relationships between them. i know i can use a table from one database in another database, but sometimes i need to import more than one table and the relationship between them is not exported... also if i have three files, i have to define privileges for three times. and if is better to use one field with tree connected databases, how can i copy the database's tables and fields into another file? thanks for each reply... sju

[T-Square]

Unless you have a compelling reason (e.g., you don't have physical jurisdiction over one dataset, or you have one EXTREMELY large dataset), you will be better off with one file and three tables in that file than separate files.

David

[sju]

ok, but i have three files in each one there are four tables with relationships. for example if database file is for orders and another for emloyees. but i need a relationship between them to insert information(only a piece) about employee who responds for current order

[bruceR]

So? What is your question? And no, you **really** do not want to copy tables between files. That's what file references are for. It doesn't matter which file actually holds the table.

Edited August 23, 2006 by Guest

[sju]

ok, thaks.. i' asking because i don't know, if i should have problems later because of that.. mainly when i'll be solving access privileges...

[T-Square]

So, for each file you have at the operating system level, you will need a parallel set of accounts and privileges. Moreover, for each file you have open, you run the risk of corruption between files if FM crashes (that was one of the big bugaboos for me in earlier versions of FM). All of this can be handled, but you end up making work for yourself (hey, maybe you're looking for a lifetime sinecure!).

So, I'll repeat my suggestion that maintaining a solution is substantially easier when it is in one file. If you gather your tables into one file (note that I am NOT recommending that you copy data back and forth), your job as developer will be a lot easier down the road. If you invest the time now to take the three files with four tables in each and merge them into one file with 12 tables, you will reap benefits long into the future.

I guess now the question I would pose back is this: why *wouldn't* you put all your tables into one file?

David

[Ender]

Hey David. Although I agree that it is easier to keep everything in one file with a small solution, I'm afraid I don't agree with this statement:

Moreover, for each file you have open, you run the risk of corruption between files if FM crashes (that was one of the big bugaboos for me in earlier versions of FM)

I don't recall coming across any evidence of this in the forums, nor have my own experiences shown stability problems inherent with multi-file solutions.

On the contrary, some reasons it may be desirable to use multiple files for larger, complex solutions is 1. to limit the extent of damage from file corruption, and 2. to improve the speed of rebuilding/recovery operations should a file become damaged.

[sju]

so, why i want to use multiple-files? i think the access to the data should be faster, if you have to open one of three smaller files than one big with amount of data. if somebody want to add an employee, he/she don't need to have possibility to see anything else and if he/she would like to, there can be a button to open other file with data.

[T-Square]

Ender--

I certainly envy your (lack of) experience with corruption of data with multi-file solutions. It was my dubious privilege to have to deal with this on a regular basis under FM6 and earlier. (I should think my post was clear that this was a problem that *I* had with earlier versions of FM.)

You say you found no evidence of this in the forums; I previously posted a thread (titled "Relationship Corruption") back in 2003: http://fmforums.com/forum/showtopic.php?tid/63242/post/63242/#63242

Specifically, if a user had to force exit from FM in earlier versions, FM would trash the relationships between files, and I would have to talk my (non-technical) users through the process of re-creating the relationships. This happened frequently--so much so that those same non-technical users got to be familiar with the "Relationship Rebuild Drill".

I guess, technically, what I'm describing isn't data corruption, per se (since no data were actually harmed)--but try telling that to your client who's just "lost" all the related information...

I will confess that I haven't tested v.7 or 8 for this trouble; because of my earlier experiences, I made sure to get my tables into the fewest number of files as quickly as I could. So maybe this behavior has been changed in 7/8.

David

[Ender]

From the responses in that thread, it looks like your problem was caused by something with your particular deployment. I just don't think it's helpful to generalize too much about the extent of a problem without some confirmation of the problem in other solutions. And since FM7/8 handle multiple files very differently, your previous problem may not be a problem in the new version. Or if your problem was caused by a slow network, bad file references, or a corrupt file, then there might have been other remedies.

As I said, there are some reasons for using separate files. Usually when you're dealing with large or complex solutions, or when building a Separation Model solution.

[T-Square]

Ender, I defer to your greater experience.

All I have to work with is my own experience, which was as I described it. Sorry that I shared it in a way that implied universality; I thought I made clear that these experiences were personal ones.

David

P.S. I consider the separation model to be in a different class, since the files exist to supply different functionality.