How to make a table with fields that are other tables in MySQL?

Question

I am making a music library system with php in MySQL. In this system there would be a table named Songs. Since many songs can be by the same Artist (Person), I wanted to store a table of Person that contains a persons data (name, DoB, hometown, fan page URL), then just store that ID in the Songs table.

I'm trying to avoid repeating the same 10 fields for an Artist over and over for each of their songs. I'd rather store the ID (Primary Key) and then be able to pull all of their specific data.

Two questions:

1. What is this called, so I can research how to do it (or can someone give me an example)?

2. If I do this, will queries for "Barry Manilow", who's ID may be '123', be found in every song that contains the field Artist='123'?

Thanks, -CJJ (Basically, what's the best way to reuse fields)

Answer 1

This is a really basic example of normalization.

Typically you set up a foreign key relationship. In your example you would have two tables:

Artists - id, name, etc...

Songs - id, artist_id, name, etc...

You would retrieve needed data from Artists using a JOIN. You should look into some entry level SQL tutorials. JOINs are the bread and butter of SQL, and what makes it a relational database.

• RolandoMySQLDBA: I fixed your JOIN linked. +1 for your concise and direct answer.