Sorting out the tables on a database?

Question

I need to create a database for a project...and the categories I need are: Surname, Forename, Month of trial, Year of trial, Gender, Crime, Verdict, Punishment, Accomplice.

Could anyone suggest how to break that information down into tables? And what the primary key linking them would be?

Many thanks for any help anyone can give - I'm a complete novice!

Answer 1

The process is called 'Normalisation'. In full there are 5 steps to this process but the first produces the most useful results - All repeating groups should be moved to their own tables. Here however it is difficult to say exactly what you should do as normally you need to know what the system is supposed to answer and in practical systems this often determines how the data is structured i.e. actual systems are a compromise and very rarely the theoretical ideal. Google 'Normalisation of data' to get practical examples.



Edit: Based on the other answers so far it is not as simple as is being suggested. As one example an Accomplice in one or more offenses could also be the main offender with some other accomplice(s) in one or more other offenses. Under these circumstances the 'person' table should include both the main offender and the accomplice with appropriate links to all their crimes. This is what I meant by saying you need to know what the system is supposed to answer before deciding on exactly how to structure the data.

Answer 2

A person table for the gender, forename and surname with a primary key numeric id (always best) which under mysql would be :

id int(6) auto_increment

Then a crime table for offences again with a numeric id

A verdict table (a list of possible verdicts with ID)

A punishment table similar.

Then a table to combine all the ids to link the person.id to the crime.id, verdict.id, punishment.id and another person.id as the accomplice

This should include the date as one field. From this it is possible to generate any type of report you want

You might want the gender to be a simple boolean, but I prefer to use a simple int(1) field with the alternatives being 0 and 1. For this use not such an important difference. In other cases it can present limitations to use boolean. I have had cases where a table had been designed to use boolean for valid and invalid availability (obsolete / current) for something, and then there was a call for something to be identifiable as invalid from different actions in the database. Changing this field to numeric allowed for multiple invalids, each identifying the actual source of the invalidity. This in turn meant the whole system could automate changes to re-validate the item, except for the 0 case which was still obsolete with no remedy.

Answer 3

Person_tbl:

Person_ID

Surname

Forename

gender



Trial_tbl:

Trial_ID

trial_month

trial_year



Person_Accomplice_tbl:

Person_Accomplice_ID

Person_ID

Accomplice_ID



Charge_TBL

Charge_ID

Charge/crime

trial_ID

Verdict

Sentence



Anything that says ID is a primary or forgein key.