0

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?

0

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';
  • Thanks for the advice. I thought about it after I wrote out the question. I came up with the same query too. So with respect to design I don't really need to link the tables at all? Maybe not for this but perhaps future applications... I'll check out the reference and see where I get. – square_eyes Oct 23 '13 at 10:49

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.