0

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

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.

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.

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.

  • 1
    Screenshots are discouraged here for the reasons outlined in the link. In future, could you please post DDL and DML as text, better still on a fiddle so that those who are so inclined can just run your carefully prepared scripts and not have to type anthing in manually - an error-prone and tedious process. – Vérace Nov 26 '19 at 0:33
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

| improve this answer | |
  • Hey Michael. Thank you for this answer so much. Could you explain in detail how this tables should be related in MS Access? This "customers" table is my primary table. The bulk of my data is there. The "jobs" table is the additional table - or the outside table - I don't know how to call this table professionally. Is the table you proposed and designed 4 me a junction table?? – michal roesler Nov 26 '19 at 17:10
  • There are many customers who are actors, many customers who are directors and many customers who are screenwriters, many customers who are producers and so on. If in the linking table I have only 580 records with Customer ID (this is how many individuals I have in my database), how will I assign 3 or more jobs to every individual?? I'll have have to add additional columns in the junction table, which I already have in my primary column. So how many records should this "linking table" have?? – michal roesler Nov 26 '19 at 20:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.