Best Practices - Setting up a person records in Oracle
Posted on 2011-05-13
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!