[absynthe]

Well I've picked up my first major project outside of my regular work. Im SO Excited about it but its a mess. The project involves a database that had been set up by another contractor who had obviously never did a database before. He has ONE table with 197 fields, no relations, nada. He also used FILEMAKER which is completely new to me!

The agent is a state institution set up for post release of inmates through the department of corrections. These are the snippets of the 1 table he had. Any ideas on how to normalize this thing?










Any help would be awesome! I just want to make sure I get this right and not have too many troubles later on..ugh! I know the concepts of normalization and have started some of it but its my first time and its going so slow! Any ideas on tables or relations would be great!

This post has been edited by absynthe: 06 March 2009 - 10:45 AM

[floatingpoint]

Hi,

Normalization is performed based on some functional dependencies.. and that means some rules which the organisation has or wants to deploy... without that you can possibly have millions of options.. so tell us the requirements, which fields are frequently used which are not .. the kind of queries ud expect etc

[absynthe]

floatingpoint, on 6 Mar, 2009 - 11:58 AM, said:

Hi,

Normalization is performed based on some functional dependencies.. and that means some rules which the organisation has or wants to deploy... without that you can possibly have millions of options.. so tell us the requirements, which fields are frequently used which are not .. the kind of queries ud expect etc

Yeah you're exactly right! Sorry!

Im posting some of the forms already created in Filemaker from this single table. All they are is user input from clients they have come in post release. All the fields are used each time a new client is entered into their system. The queries they want are search and update queries for each of the clients entered (like if i wanted to find bob jones or whoever) and a few queries to set up a viewable list of clients entered in a week, queries for followup with clients after 3 months. The majority of the queries refer to the clients and their status at work, home, etc. They just want a way to input them and then update them later mainly, print clients they have entered and keep tabs on them.


Here are the screenshots of the forms set up like the way the Case Manager will see them to input each client.

[xerxes333]

Well at quick glance I would stay start out by separating the client personal info into a table (although I'm sure you figured that much out already:). Next I would create and income, assistance, inst. programs, offenses, and jobs all with respective PK's and FK's. just my $0.02

[absynthe]

I divided out "employer table" and "employment table" made a many to one join and made primary keys out of employer ID and Employment_ID with employment_id being a fk in employer table. thats all I have so far..

thanks xerxes!

Keep ideas flowing! Thanks so much guys!

[Trogdor]

Some things that are probably giving you grief are the fields such as
military_rank text
homeless_status text
on_parole text

These kind of values should preferably be limited by the database, not by the application. (not everyone might agree here though)
What you could consider is to make a stem table for each such field with all possible values. So for military_rank you get a stem table
table: military_ranks
id: int sequence/auto_increment
rank: text

And fill that with the ranks that can be found in the database at this moment (i assume you have a copy of it) and perhaps some ranks you can think of yourself.
Then you replace the field military_rank:text with military_rank_id:int
Make sure you can set the _id field to null, indicating no rank. Or include a record with no_rank, that would work fine too.

Repeat this for all similar fields.
That will solve a lot of problems with garbage entries.


Then there is things like homeless_status:text.
Can someone be anything else then either homeless or not?
I guess a char(1) field should be enough, filled with a 'Y' or a 'N', or a boolean if your database supports it.

Something else to consider is this: i am not sure how large this database is, but i suspect there to be more then 10.000 records. In that case porting it can be problematic (as in a huge problem, people shouting at you and long nights of sweating behind a terminal).
So here are some tips.
- Test with real data. Get a snapshot of the life database and use this copy to work with.

- Do it in steps. First from the original format to a similar huge table in your target database.
Then write a program to split the table up.
then write another one to fill the stemtables and refine your data further.
etc, until all is finished.
When all of that works with the original data, you can try to do the whole procedure on a recent snapshot. It should run in one go.
It should also warn you when there is data that can not be converted correctly. You should make sure that ALL data can be converted, otherwise 'people' will get lost!

- consider writing the frontend from scratch. Poking around in other peoples code is never a party, but judging from the database design in this case it will be hell.

Good luck with it.

[xerxes333]

Trogdor, on 6 Mar, 2009 - 02:47 PM, said:

...
What you could consider is to make a stem table for each such field with all possible values. So for military_rank you get a stem table
table: military_ranks
id: int sequence/auto_increment
rank: text

And fill that with the ranks that can be found in the database at this moment (i assume you have a copy of it) and perhaps some ranks you can think of yourself.
Then you replace the field military_rank:text with military_rank_id:int
Make sure you can set the _id field to null, indicating no rank. Or include a record with no_rank, that would work fine too.
...

I agree the dragon-man-with-the-beefy-arm man, but I suppose it depends on how they want the user to interact with the form. If the user is limited by possible ranks available in the system and they try to enter a rank NOT in the system they then have to go through the process of creating said rank. Or they could auto create any new ranks not found in the system but that opens the door for possible user errors. Just something to keep in mind.

[absynthe]

Thank you so much! Thanks for this esp:

Quote

So here are some tips.
- Test with real data. Get a snapshot of the life database and use this copy to work with.

- Do it in steps. First from the original format to a similar huge table in your target database.
Then write a program to split the table up.
then write another one to fill the stemtables and refine your data further.
etc, until all is finished.
When all of that works with the original data, you can try to do the whole procedure on a recent snapshot. It should run in one go.
It should also warn you when there is data that can not be converted correctly. You should make sure that ALL data can be converted, otherwise 'people' will get lost!

- consider writing the frontend from scratch. Poking around in other peoples code is never a party, but judging from the database design in this case it will be hell

I am re writing the front end, it was almost as bad as the database.

I am probably f'd though. I have never heard of a stem table!!! Am I retarded? Did I miss a day of class? I've just never heard of them. Could you explain this?

Especially how this works:

Quote

What you could consider is to make a stem table for each such field with all possible values. So for military_rank you get a stem table
table: military_ranks
id: int sequence/auto_increment
rank: text

And fill that with the ranks that can be found in the database at this moment (i assume you have a copy of it) and perhaps some ranks you can think of yourself.
Then you replace the field military_rank:text with military_rank_id:int
Make sure you can set the _id field to null, indicating no rank. Or include a record with no_rank, that would work fine too.

Xerxes: there are ranks limited to the system..so no stem table then?

[xerxes333]

Im not sure what he means by stem table either, probably just a culture jargon difference.

If you know all the possible ranks the user can enter into the system you can have a separate table with all the ranks and unique ids and PK/FK it. You can then use the id from the Military_Rank table so your queries are faster rather than doing a text comparison on the actual text of the rank.

[JackOfAllTrades]

I need to get a government job...it may be the only way I can ever retire...

That is some crappy "design" right there. Sorry you have to deal with it, absynthe, but I hope they're paying you well!

[absynthe]

Good, I thought I was the only one! Thought I fell asleep or the like!

Thanks, I can use that for a few of the tables actually I think!

Dont stop though, any other help would be great! If you see the best ways to break the other tables out feel free to let me know it! Just want to try and get it right first time through!

[Jayman]

I have never heard of it referred to as a stem table, but what he is talking about is a reference or lookup table. Typically, this is data that does not change very often.

[absynthe]

JackOfAllTrades, on 6 Mar, 2009 - 03:40 PM, said:

I need to get a government job...it may be the only way I can ever retire...

That is some crappy "design" right there. Sorry you have to deal with it, absynthe, but I hope they're paying you well!

Ill be honest with you! LESS THAN FIVE GRAND..and I have to redo the front end! No kidding! The original designer got more than twenty! Now Im kind of picking up the pieces..

Jayman, on 6 Mar, 2009 - 03:41 PM, said:

I have never heard of it referred to as a stem table, but what he is talking about is a reference or lookup table. Typically, this is data that does not change very often.

Now I have heard of reference tables! Thanks Jay!

I know how good you are at this, do you have any ideas?

[xerxes333]

absynthe, on 6 Mar, 2009 - 03:40 PM, said:

Good, I thought I was the only one! Thought I fell asleep or the like!

Thanks, I can use that for a few of the tables actually I think!

Dont stop though, any other help would be great! If you see the best ways to break the other tables out feel free to let me know it! Just want to try and get it right first time through!

One thing I've learned over the years is that you NEVER get it right the first time! If you did your job would not be very secure

[Jayman]

absynthe, on 6 Mar, 2009 - 01:45 PM, said:

I know how good you are at this, do you have any ideas?

I have all kinds of ideas. Good ideas, bad ideas, sexxxy ideas, and ideas that would probably get me in a lot of trouble if they ever got out of my head. But that is my dirty little secret.

However, if I do all the work, does that mean I get all the money?