Combining Databases

[Alisun]

I am using FileMaker 8, and have three databases, each for a type of women's health issue. The three databases need to be combined. They all have the following, which are unique:

~500 records

~250 defined fields

~50 value lists

The above are organized into 8 tab panels each. Is there a way to (fairly painlessly) combine all this information into one large database?

Thanks!

Alisun

[Ender]

Is the data homogenous (does it fit into the same table structure), or are they about different types of things?

[Alisun]

They are onocology databases: cervical, uterine and ovarian. They need to be combined into one large database, which will be called "gynecologic oncology".

Ideally, I would like to have a Tab panel for each type of data, so that all three databases could be accessed easily from one screen.

If that is not possible, each could have it's own layout (accessible by FileMaker's left-hand drop down menu).

Either way, I think it would be best to import the data into one database. I am concerned that if I create relationships, the related files could be accidentally erased. So, I'd like to have all the info in one database.

Any tips are appreciated!

[Tricky]

Interesting subject and useful to put into a database. You are using FM8 but the data is in an earlier version? Or did you build all three of them from scratch in FM8?

[Alisun]

Hi, all three were built from scratch in FM8...

[Tricky]

As in: three separate databases, or three tables in one database?

[Alisun]

By the way, the database will be used for studies, so having all the info in one location would be ideal for research purposes.

[Søren Dyhr]

~250 defined fields

I raised my eyebrows when seeing this, and browsed your previous posts to get some ideas what we're up against.

It seems like you have no idea if a distinctive question you ask your patients really is the same as a generic field description:

http://www.databasedev.co.uk/database_field_names.html

Further more does your previous post, show that the fields required are highly individual from patent to patient, which goes against the rigidness usually associated with databases, simply put isn't you fields really fields, can you prefix each of your fields with the table name? ...e.g. Name in the patients table could meaningfully be said as Patient_Name.

Next issue is ....Although it probably is pretty obvious to you what the collected data is used to ...do we here sorely miss a mission statement regarding the task the base is supposed to provide the user. It would be half-hearted to perscripe universal "database painkillers" just in vain.

Is it searches, is it datamining or arbitrary eyeballing? What kind of output should the database produce that the legacy database weren't up to?

This field not really being fields issue, makes it pretty tough to guess if you better would be served using a database structure like this:

http://previews.filemakermagazine.com/videos/513/DataTagging_full.mov

...or it makes the querrying you wish to do inconvenient? The use of a lot of valuelists point in direction of dynamic generation of valuelists of you adabt tagging idea just mentioned.

--sd

[Alisun]

They are three separate databases.

But that brings up a good point - I could put the three tables into one database.

I'm still a newbie, and am excatly not sure how...

[Alisun]

Wait - I think I have it. Adding tables is not as difficult as I thought. I will also have to import records, as well.

Soren, those are good questions. My research group is collecting a large amount of data, and although not all fields will apply to each patient, all the fields are necessary.

For ease of searching, sorting and creating reports, it might be best that the information is in place. Hence, the reason for wanting to combine the databases.

-Alisun :P

Edited June 22, 2006 by Guest

[Søren Dyhr]

For ease of searching, sorting and creating reports, it might be best that the information is in place.

Hopefully can you see in how verbose you're keeping it, frankly does it only indicate that a database might be up to it - simply because it's the reason why we pick up databases as tools in the first place ...almost every database application falls into such broad a difinition.

You could abstractly exchange your research group, with say a sales staff or whatever and they will all come up with same kind of statements. But in order to get closer to the solution you require, do we need to know what it takes to make raw data into information in your case.

The specific purpose of the database is unclear - What does the searches typically search for? What does the reports contain when produced? Are the mission to be bluffing with a kind of ketchup effect to overwhelm our recievers of information, in order to land our next commission or are we interested in knowing weird coherence between otherwise unrelated data.

We can't give proper advice, if we just serve as rubber stamps for desicions already taken, the mission statement you came up with only vaguely indicate that a database might be more up to it than a spreadsheet.

I hope you can see that the bluffers system requires an entirely different structure compared to the ones that provides a view to spot patterns otherwise unrelated - and that in fact both of these statements makes them gestalts on the perimeter of what databases serves when at their best.

Without such narrowing in the scope of the solution, is it going to be a rubbish heap, that only weirdly talented or autists can make any sense of.

--sd

[Tricky]

Alisun: I believe that you need to first set up a schema for the new database. Which are the values to be put in and which are the results you wish to get out. I can imagine that things like general names and medical terms would be valid for all three groups. And even if some of the fields would not be valid for all three, this does, in my humble opinion, not validate the choice of three tables. The fact that the CONTENTS of the fields suggests differences, does not mean the the FIELD that the contents populate are different as well.

Take care not to fall into the trap of: new disease, new field. This makes it impossible to search.

In your case I would really opt for a starting point of a single table database. If you see the database as a desk, the table is one of the drawers in it, and the fields to hold information are folders in that drawer. The only real validation for another table, I believe, would be if the type of information stored in it differs totally or for an important part from the information in the disease table. For instance: patient data.

In terms of usage: you say research is an important objective of the solution. This makes it even more important you set up the model appropriately.

The Pentagon, so I have been told, uses Filemaker for some of its criminal research. Now imagine that they would create separate tables, or worse: separate databases for criminals who do crimes with knifes, guns and bombs. I cannot even begin to imagine how much convoluted scripting and linking they would have to do to find what the three groups might have in common. If, however, all crime descriptions were in the same table, and a search was possible on a field that contains, for instance: weapons of assault, it would not be a big thing to find all that use knives and/or guns.

[Alisun]

Thanks for all your suggestions!

I should have given a more detailed explanation. If a patient has more than one type of health issue, for example, both cervical and uterine cancer, then the data had to be entered in two separate databases.

Good news, I have figured out how to import records into a combined database, so there will no longer be duplication of efforts.

Thanks again for your thoughtful responses!

Alisun