how to professionally normalize my database - screenshots included

Question

Hello database administrators.

I got some excel spreadsheet with data of "my customers" and I'd like to create professionally designed database out of it. Can anyone tell me how this data should be normalized, what tables should be created and how should they be related with each other, so that my database would meet corporate industry standards?

I know I'm just a beginner in database creation but I've already learned about concept of database normalization, I'm eager to learn and I'll be grateful for any help and directions.

Here is the first chunk of my data imported to access table. As you can see I've already changed the job's names into numbers and I have separate table where these numbers are assigned into the jobs names. Here it is in jobs table.

Jobs table with jobs names.

Here is the rest of the data - please help me organize it.

How about this *BESTMovie columns - do they need normalization? Some movies repeat 10 times and others don't repeat at all.

And one more question to this "*Profession" and "*BESTMovie" columns.

If I had some person, lets say clint eastwood or robert de niro, that I'd like to assign not only 3 but 5 movies, how to do this without adding another "*BESTMovie" column, where most of the fields would be blank for some other people who didn't have more than 3 movies?

I've heard about some rank column or rank table but I have no idea how should it look like.

The rest of my data looks like this:

Here is an "EyesColor" column and "Adjective" I use to describe a person. Can these columns be normalized and should the be normalized? The eyes color repeats many times of course, and adjectives repeat less frequently but they do repeat.

I hope the question is well put and I'm looking forward for answers from you guys.

Answer 1

You have

• a customers table.
• a jobs table.

You normalize the 3 Profession columns by moving that information into a linking table which contains

• Customer ID
• Job ID
• something to represent "1st","2nd","3rd" Profession
• Constraints to ensure the data is valid

To get the data back in the original form, use a VIEW.

Example:

create table customer_jobs (
    customer_id int not null
    ,job_id int not null
    ,order_by number(1) not null -- defines "1stProfession","2ndProfession","3rdProfession"

    -- ensures that each customer has only one "1st Profession", not two "1st Professions
    ,constraint customer_jobs_pk primary key (customer_id, order_by)
    -- ensures each customer claims a job once and only once
    ,constraint customer_jobs_uq1 unique (customer_id, job_id)
    -- ensures that the customer is a known customer
    ,constraint customer_jobs_fk1 foreign key (customer_id) references customers (id)
    -- ensures that the job is a known job
    ,constraint customer_jobs_fk2 foreign key (job_id) references jobs (id)
    -- Limits to only 1st, 2nd, and 3rd Professions
    ,constraint customer_jobs_ck1 check (order_by between 1 and 3)
);

Repeat this method with the *BestMovie columns