Need expert advice on search-possibilities. Please...

[Mirador]

Hi and thanx a LOT for reading my post..

I'm quite lost here and VERY near deadline so yes.. i'm a bit stressed out

I'm creating this criminal investigation information -"dump" database which should be searchable every- and any way possible..

----------------------GENERAL INFO---------------------------------
In general it's 5 mainforms -
* Person (PID)
* Vehicle (VID)
* Location (LID)
* Scenary (SID)
* Telephone (TID)

Each and everyone of these forms got their "relation-form" like this example :

PersonTelephone (PTID)
Fields :
*PID (auto-inserted if user came from Person form)
*TID (same as above ex. Telephone form) (user picks which telephone should be "related" to the person via lookup)
*Role (which "role" the user has on the selected telephone)
*Date
-----------------------------------------------------------

I have tried to make this search-form with 1 searchfield and one button. Then i got x numbers of labels which displays how many "matches" i find in each relation-form (ex VehichleLocation) and a "display results" button which opens up a "table" with the results, but since i got about 15 relation-forms it's not a good way to handle it..

-------------What i wonder about : -----------------------------------
1) How can i, in a better way, search multiple relation/connection forms based on information input by the user in ex. 2 searchfields ?

2) and.. how on EARTH can i display the results in a good way ?

3) Is there any good article anyone knows about which "tutorials" me through this in any way ?

I hope someone is able to help me with this issue.. it seems my knowledge has meet the edge when it comes to this

Best regards.. and have a HAPPY easter-holiday..

Terje..

 

[The_Doc_Man]

Your problem isn't necessarily a pure database question.

The first rule to remember with databases is that when they are used to track some aspect of your business, the database is a MODEL of what you do. So the very first question you must ask is, If I didn't have this database but instead had something on paper, how would I do what I wanted to do?

If you cannot answer this question, you will never answer the equivalent question for having a database. The reason is that until you understand the basis of the model, you are never going to understand the model.

Now, as to how to do a search...

Your problem includes the fact that you have too many "IFFY" cases where you might or might not have a link between tables. I.e. some fields are filled in when you got the info from another table, others might be directly filled in by someone who just popped up a form and entered raw data. The design means that you cannot do a reliable join in all cases. And THAT is going to seriously complicate matters. (Not make them impossible - but really raise the cost in terms of skull sweat.)

My best advice is to step away from the details for a while and look at the problem in overview. If you can do that, you might see something that will help you. I regret that I have nothing more specific to add.

 

[Mirador]

Hi and thanx for your reply

You make some very good points there Doc_Man. I have "quested" a bit around the problem and came up with a solution that miiight be okay enough : Just posting it to get your aspect of view on it -->

This may be interessting for others to view too, since it's a quite complex and non-standard solution (atleast i think so

First a little about the purpose of the database. There are relations between the 5 forms but that will be done manually by buttons that "connect" the ex. Person to a ex. Location after the info has been stored.

Lets say it's a crimescene where the only clues are :

1) A red Opel has been spotted by a witness..
2) A mobilephone was found on the spot.

Soo... okay.. We've got info for Vehicle, Telephone, Location and Scenary.
No people yet..

The information is inserted into the database, and the
1) Vehichle (VID), Telephone (TID) and Location (LID) is connected to the Scenary (SID) by 3 different "connection forms".
2) The Telephone is altso connected to the Vehichle and Location.
3) The Vehicle is connected to the Telephone and Location.

4) After further investigation there are people which are connected to this Crime. Therefore.. Information about the Person is inserted into the database and connected to all the other forms in the specific case.

This might be a bit timeconsuming for the users, but the connections here are VERY important to track back when they want to search for ex: "What cased have phonenumber "98456787" been in ?"
This way they can track one crime with another and see simularities. Maybe the same car has been used in a simular crime ?

There will be a massive use of connection forms but that might be nessesary to get the results they want.

Ex Person will have 5 different connectionforms to it :
Person-Person
Person-Vehichle
Person-Telephone
Person-Scenary
Person-Location

When it comes to the searchform, i think it might be best to have a "result-label" for each of the connectionforms. The total number will be 15. which displays if there's a match or not and how many matches. Next to it i'd put a "Display results" button which opens a table-view of the results.

The only thing i'm not exactly sure about is how i should categorize the searchresult information. Might be best to do a data-export to excel and handle it there.. not sure Any ideas ?

Best regards
Terje.