Structuring import of related data

[AlanJ]

I need to import information from an extract of a large database. I can request the extract in various formats such as Excel or Tab Deliniated Variable.



Some of the records to be imported will have a one-to-many relationship. Examples: (1) One inmate has one or more people to notify (victims, interested parties, family members) when there is a change to his sentence structure or he/she is approved for parole, etc.

What's the best way to ask for the data? This is what I was going to say. I'm not at all sure this is the best way to proceed.

Thanks in advance,
Alan

Please provide all of the data to recreate one-to-many and many-to-many relationships. Please also provide a listing of each of the field names, in the same order as the information is exported. If necessary create different record types that can be related through a combination of primary and foreign keys.

“1INMA”
OFFENDER_ID FIRST_NAME MIDDLE_NAME LAST_NAME Other data

“2PTBN” Contact_ID OFFENDER_ID Other data

[rpeare]

if you're still working on this problem can you supply an example of your data (I'm assuming it comes to you in a single file?)

[AlanJ]

Thanks for the response.

Actually, no. I don't have the data, and I don't know that it will come in a single file. I'm at the point where I've been advised to ask for the data with a request that is similar to this:

The Parole Board would like to run a periodic extract of information from NOTIS, to be placed in a Tab Separated Variable text file with double quote marks placed around data.
e.g.
"0001065263" "Webb" "Rodney" "Norris" "04/05/1948" "2011-056139" "LCC-U1-A-83B, :, NC" "Person" "359387" "262310" "Victim" "359387"…

Please provide all of the data to recreate one-to-many and many-to-many relationships. Please also provide a listing of each of the field names, in the same order as the information is exported. If necessary create different record types that can be related through a combination of primary and foreign keys.

We would like to have the Offender Number, Inmate Name, and full contact information on each person to be contacted, along with the type of interested party. (i.e. Victim, Interested Party, etc.) Please include the notification options that were selected for each person to be notified.

Any suggestions for improving the request will be appreciated.

[rpeare]

Can you tell me who wrote the portions of that quote? It looks like part of some documentation you're supposed to supply (a request for data) followed by what you typed (the last paragraph)

If you look at it this way, each inmate can have any number of possible Contacts, and each contact could be associated with multiple inmates so you're really dealing with a many to many table structure with a junction table to help you determine who goes with who:

So let's say you have Inmates 1 2 and 3
You have contacts 1 2 and 3

Now let's say (for the sake of argument) that your contacts are lawyers and each inmate is working with two lawyers

You could theoretically have one table holding both inmates and other contacts but let's assume you don't

You'd probably want to request 3 separate 'tables' in your extract

1. A list of inmates with your important information
2. A list of contacts with any important information from them
3. A list of which contact goes with which inmate (among this information you must have the offender number or something that can be directly tied to the inmate information uniquely)

If you can't get #2 it's not a huge deal you can get #1 and #3 and do it just fine it's just that you may have multiple values with the same information (again let's assume you've got a lawyer representing more than one inmate, that lawyer would appear once for each inmate represented)

[AlanJ]

Thanks again for your response.

Yes. I wrote the information in the quote. Originally, I started by asking for fields that were associated with different screens. However, I do not have access to each field name, so I took this more generic approach.

I'm not sure if it is a many-to-many relationship or a one-to-many relationship. One inmate to one or more interested parties. (Interested parties could be victims, lawyers, family members, family of victims, general public.)

I have stayed away from requesting an associative table. The organization from which I am requesting the information, does not particulary want to share it, even though it is our data. It's important that I make the request in a way that is easy to fulfill, and that they do not have an opportunity to provide incomplete data and then blame it on us for not filing an appropriate request.

Alan

[rpeare]

There's no way you can request an 'easy' query from them I'm afraid, without knowing their data structure to start with.

If you want to be generic, request the piece of information you need (both inmate specific and interested party specific) and see what they give you.
Alternately you could ask them what would be easier for them to give you two tables, one with the inmate information and one with the interested party information with a linking offender_ID

Be specific about the information you need and let them determine how to get it to you, if you don't know their structure trying to create a structure is likely going to cause more problems for them.

[AlanJ]

Thank you. I sincerely appreciate your input.