Mysql – Build a simple query and relationship table

MySQL

This is my first post here. Thanks for having me!

I have two questions for a table I'm building into my mysql database.

Question 1: I have a need to input an artist name and the result/select be a distinct twitter handle. How might I build this query as efficiently as possible based on an optimal solution for Q2 (Preferably in one line).

Question 2: Based on the requirement above how should I build my table 'twitter' into the database. Below is a start, but happy to discuss best practice.

Simplified Example: Input = 'Beatle'. Desired output '@beatleMe'

table: songs (note artist is not distinct)
ID  ARTIST  SONG
1   Mike J  Beat It
2   Beatle  Yellow Sub
3   Beatle  Lucy Diamond

table: twitter (note artist is distinct)
ID  ARTIST  HANDLE
1   Mike J  @mikeJ
2   Beatle  @beatleMe

I'm from an excel background, so I think in terms of lookups. But I know I can get more out of my database with foreign keys, joins etc (if they are relevant here I'm not 100%). So am keen to learn.

The song table will be maintained most regularly, so it would be good to have the artist columns mirrored/linked is that possible?

Best Answer

A good starting place is to research database normalization. It explains some concepts on how to best structure tables.

More specifically, I'd name the tables "Song" (singular is often preferred) and "Artist" (probably a more central concept than a twitter account). A song would then have an ArtistID which refers to the Artist table. To get the twitter account for an artist you only need to look at the Artist table, you don't need to look at the Song table at all, e.g.:

select TwitterHandle
from Artist
where ArtistName='Beatle';
Related Question