Best Practices - Setting up a person records in Oracle

Posted on 2011-05-13
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
    LVL 7

    Accepted Solution


    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.

    LVL 1

    Author Comment

    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.
    LVL 7

    Expert Comment

    LVL 40

    Assisted Solution

    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.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Highfive Gives IT Their Time Back

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
    Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
    This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
    This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

    758 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

    Need Help in Real-Time?

    Connect with top rated Experts

    13 Experts available now in Live!

    Get 1:1 Help Now