Normalizing a Database

Huge side job

  • (3 Pages)
  • +
  • 1
  • 2
  • 3

44 Replies - 2340 Views - Last Post: 03 April 2009 - 10:22 AM Rate Topic: -----

#1 absynthe  Icon User is offline

  • DIC Tease
  • member icon

Reputation: 28
  • View blog
  • Posts: 2,807
  • Joined: 20-September 08

Normalizing a Database

Posted 06 March 2009 - 10:37 AM

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?

Posted Image
Posted Image
Posted Image
Posted Image
Posted Image
Posted Image
Posted Image
Posted Image

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


Is This A Good Question/Topic? 0
  • +

Replies To: Normalizing a Database

#2 floatingpoint  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 34
  • Joined: 05-March 09

Re: Normalizing a Database

Posted 06 March 2009 - 10:58 AM

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
Was This Post Helpful? 0
  • +
  • -

#3 absynthe  Icon User is offline

  • DIC Tease
  • member icon

Reputation: 28
  • View blog
  • Posts: 2,807
  • Joined: 20-September 08

Re: Normalizing a Database

Posted 06 March 2009 - 11:48 AM

View Postfloatingpoint, 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.

Posted Image
Posted Image

Posted Image

Posted Image

Posted Image
Was This Post Helpful? 0
  • +
  • -

#4 xerxes333  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 31
  • View blog
  • Posts: 504
  • Joined: 05-July 07

Re: Normalizing a Database

Posted 06 March 2009 - 11:55 AM

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
Was This Post Helpful? 0
  • +
  • -

#5 absynthe  Icon User is offline

  • DIC Tease
  • member icon

Reputation: 28
  • View blog
  • Posts: 2,807
  • Joined: 20-September 08

Re: Normalizing a Database

Posted 06 March 2009 - 12:19 PM

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!
Was This Post Helpful? 0
  • +
  • -

#6 Trogdor  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 15
  • View blog
  • Posts: 627
  • Joined: 06-October 06

Re: Normalizing a Database

Posted 06 March 2009 - 01:47 PM

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.
Was This Post Helpful? 0
  • +
  • -

#7 xerxes333  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 31
  • View blog
  • Posts: 504
  • Joined: 05-July 07

Re: Normalizing a Database

Posted 06 March 2009 - 02:17 PM

View PostTrogdor, 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.
Was This Post Helpful? 0
  • +
  • -

#8 absynthe  Icon User is offline

  • DIC Tease
  • member icon

Reputation: 28
  • View blog
  • Posts: 2,807
  • Joined: 20-September 08

Re: Normalizing a Database

Posted 06 March 2009 - 02:21 PM

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?
Was This Post Helpful? 0
  • +
  • -

#9 xerxes333  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 31
  • View blog
  • Posts: 504
  • Joined: 05-July 07

Re: Normalizing a Database

Posted 06 March 2009 - 02:36 PM

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.
Was This Post Helpful? 0
  • +
  • -

#10 JackOfAllTrades  Icon User is offline

  • Saucy!
  • member icon

Reputation: 6090
  • View blog
  • Posts: 23,600
  • Joined: 23-August 08

Re: Normalizing a Database

Posted 06 March 2009 - 02:40 PM

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!
Was This Post Helpful? 0
  • +
  • -

#11 absynthe  Icon User is offline

  • DIC Tease
  • member icon

Reputation: 28
  • View blog
  • Posts: 2,807
  • Joined: 20-September 08

Re: Normalizing a Database

Posted 06 March 2009 - 02:40 PM

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!
Was This Post Helpful? 0
  • +
  • -

#12 Jayman  Icon User is offline

  • Student of Life
  • member icon

Reputation: 418
  • View blog
  • Posts: 9,532
  • Joined: 26-December 05

Re: Normalizing a Database

Posted 06 March 2009 - 02:41 PM

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.
Was This Post Helpful? 0
  • +
  • -

#13 absynthe  Icon User is offline

  • DIC Tease
  • member icon

Reputation: 28
  • View blog
  • Posts: 2,807
  • Joined: 20-September 08

Re: Normalizing a Database

Posted 06 March 2009 - 02:45 PM

View PostJackOfAllTrades, 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..

View PostJayman, 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?
Was This Post Helpful? 0
  • +
  • -

#14 xerxes333  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 31
  • View blog
  • Posts: 504
  • Joined: 05-July 07

Re: Normalizing a Database

Posted 06 March 2009 - 02:48 PM

View Postabsynthe, 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 ;)
Was This Post Helpful? 0
  • +
  • -

#15 Jayman  Icon User is offline

  • Student of Life
  • member icon

Reputation: 418
  • View blog
  • Posts: 9,532
  • Joined: 26-December 05

Re: Normalizing a Database

Posted 06 March 2009 - 02:52 PM

View Postabsynthe, 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?
Was This Post Helpful? 0
  • +
  • -

  • (3 Pages)
  • +
  • 1
  • 2
  • 3