Best Practices - Setting up a person records in Oracle

Posted on 2011-05-13
Medium Priority
Last Modified: 2012-05-11
Good morning all - I was curious how other people would go about a scenerio, I'm looking for what would be considered the best practices and do not have enough experience in Oracle to be 100% sure I am designing this database correctly in the beginning.

I am trying to come up with a database solution that will allow many users to enter peoples information in a "Gang" database so they are able to better track people when they are arrested and provide more history for gang activity in our area.

Initially I was considering 3 tables, a "Gang" table that will hold information about the specific gangs, (Colors, "HQ", general information, leaders, etc) a Vehicle table to identify any vehicles that are used often in some of these crimes and a Person table.

The person table is the one that is confusing me - I have about 50 items they want to track "Per" entry, but they never want to allow a record to be deleted once its entered - For instance if I go arrest "John Gangester" today and record all his information (Where arrested, address he gives, etc) and then 3 months from now someone else arrests him, but he is living somewhere else they want to be able to see the original information that was entered by the first officer, and enter updated information.  Say he gets arrested 8 different times this year, and his information is updated 8 different times - they want the ability to go look at a "history" and see all the different records for this guy entered by everyone.

I blew right past this without thinking it through at first but now slowing down and thinking about it - I cant come up with a way that would accomidate what exactly I want... when they are doing a search, I dont want them to see 43 entries for one person, I want them to see one person - Initially I'm thinking of having it setup that each "person" that gets entered gets a Person_number, and a "sequence" number, so one person may have 100 different "sequences"(one time for every "update"/"change" to his information..

Would other people approach this differently?  (Please let me know if I was not clear enough, Trying to get this typed out with people coming in and out and rushing to get to another meeting....)

Thank you ahead of time for any information!
Question by:hej613
  • 2

Accepted Solution

Piloute earned 1200 total points
ID: 35754354

Try to organize your data around central items that won't change... Your Person_number is a good start point. It could in fact be a social_security number...

As for the search items, I strongly suggest you to think to multi-criteria. In some places you can change (or fake) your social-security number, your name, etc. Think organizing the data in such way the main search keys to be real keys in your tables (PKs actually).

The sequence storage : since you want distinct events stored for the same person, and - i imagine - you also want to know the date for that, use the date instead of your sequence. No need to create useless data in your db. It will also be a perfect key for searches...

There's no such thing as good/bad organization of a db. Theses are only suggestions over the ideas you already have and none of your ideas is good or bad. All I can suggest, is think over and over again you architacture before you set it up. It will be a lot harder to change once done.


Author Comment

ID: 35754761
Thanks Piloute!  

Since I dont have the experience most of y'all do, I guess thats what I was trying to do, use y'all as a sounding board to see if my idea was good, or if it was a "better" way to do it in your experiences.

I would like to have a solid base so I dont have to change it later, just as you said.

Thank you again.

Expert Comment

ID: 35755666
LVL 40

Assisted Solution

mrjoltcola earned 800 total points
ID: 35756400
I'd create a Person table for the base record / information.
Then an Arrest table as a child of Person, with foreign key to Person.ID, which would hold full data at each arrest, and you could order that by date / time.

Then you are sure you capture all the data, and you can write whatever logic around that to see address changes, most current address, etc.

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
In today's business world, data is more important than ever for informing marketing campaigns. Accessing and using data, however, may not come naturally to some creative marketing professionals. Here are four tips for adapting to wield data for insi…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Suggested Courses

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question