The conceptual process of populating related tables in a database (MySql) from a CSV file

Question

I'm new to relational databases and all the material I've read covered primary and foreign keys, normal forms, and joins but left out to populate the database once it's created.

How do you import a CSV file so the fields match their related table?

Say you were tying to build a beer database and had a CSV file with each line as a record.

Header: brewer, beer_name, country, city, state, beer_category, beer_type, alcohol_content

Record 1: Anheuser-Busch, Budweiser, United States, St. Louis, Mo, Pale lager, Regular, 5.0%

Record 2: Anheuser-Busch, Bud Light, United States, St. Louis, Mo, Pale lager Light, 4.2%

Record 3: Miller Brewing Company, Miller Lite, United States, Milwaukee, WI, Pale lager, Light, 4.2%

You can create a "Brewer" table and a "Beer" table. When importing how does you connect the primary keys between the tables?

Answer 1

You define Primary and Foreign key relationships when you create the tables. Once they've been set up you can just about forget them, unless the database throws an error if you try to do something that violates the relationships.

For example, if you try to add a record into the child (foreign) table that does not have a value in the parent table, the database will complain, if the relationships are set up correctly.

e.g. Adding Record 1 into the Beer table will fail unless you have already added Anheuser-Busch into the Brewers table.

A suggestion on setting up your tables:

Brewer
    id         bigint
    name       varchar(50)
    country    varchar(50)
    state      varchar(10)
    city       varchar(50)


Beer
    id         bigint
    brewerId   bigint
    name       varchar(70)
    category   varchar(50)
    type       varchar(50)
    alcohol    decimal(3,1)

Set Brewer.Id as the Primary Key of the Brewer table.
Set Beer.Id as the Primary Key of the Beer table.
Then create a foreign key relationship between Brewer.Id = Beer.brewerId

Then manually add the brewers (because there's not too many of them)

Brewer
    1    Anheuser-Busch            United States    Mo    St. Louis
    2    Miller Brewing Company    United States    WI    Milwaukee

Then fiddle your CSV file to replace all the brewer details with the brewer's respective id, and use that to populate your Beer table:

Beer
    1    1    Budweiser    Pale lager    Regular    5.0
    2    1    Bud Light    Pale lager    Light      4.2
    3    2    Miller Lite  Pale lager    Regular    4.2

So: The brewerId links each beer to a brewer in the brewer table. That is the foreign key - each beer automatically gets all the brewer's details because it's brewerId matches the brewer's id (and you set that relationship up when you created the tables).

• Beer 1 (Budweiser) belongs to Brewer 1 (Anheuser-Busch)
• Beer 2 (Bud Light) belongs to Brewer 1 (Anheuser-Busch)
• Beer 3 (Miller Light) belongs to Brewer 2 (Miller)

This lets you do really cool suff. Say Miller was bought by another company... all you have to do is change the details in the Brewer table, and voila - all the beers that were owned by Miller now belong to the new brewer. You can also calculate sales by Brewer, state, etc.

The basic rule of thumb is that Primary and Foreign keys are like a Parent - Child relationship. The Child stores the Parent's id. This way each Parent can have multiple Children. It can get a WHOLE lot more complicated, but this will give you a very good starting approach to most of your database solutions.