By gordonbooker on

I have been creating lists of songs by using CCK to create a new content type, and giving it fields such as performer1, performer2, and date performed etc.
As a Drupal beginner, I have been extremely impressed with how easy it was to create a view that gives a list of the songs.
I can click on the songtitle field, performer field or date field to sort the list in the various ways.

First I was wondering is this the best way to go about a task such as this ?
Are there any better ways of querying databases ?

Second, I have a problem wherein if I have one song title with two performers, how can I sort the table by performers, and have the song title show up both times.
If I have just one field for song performer, and thus make two nodes for each song title, then the list would show double entries if sorted by song title.

eg
SongTitle Performer1 Performer2
Always on my mind Elvis Willie Nelson

I would like to sort my list by performer and get an entry each for Elvis and Willie Nelson.
Can you make a view where you mix together two fields?

Thanks for any help.

⋅ Categories: Drupal 5.x

Comments

Shai’s picture

Shai commented

gordonbooker,

You've come across one of the classic problems of databases. Ideally, each table in a database should only have one instance of the same kind of data. So creating fields like "performer 1", "performer 2" is a red flag for a database that might not be designed properly. Modern databases are "relational" and solve this problem typically by creating three tables instead of one for the situation you describe: One table of authors, another table of songs and a third "linking table" which links songs to authors.

Read this article on "database normalization" for a good explanation of this: http://en.wikipedia.org/wiki/Database_normalization

But getting back to Drupal and your use-case.

You create one content type "performer" with fields like "name" "birthday", "bio" etc. No fields relating to songs. Then you create a second content-type "song." Fields might be "date written", "lyrics" etc. No performer information is entered directly into that content-type. Rather you create a node-reference field (make sure to select "allow multiple values") which will allow you to select performers from a drop down menu of nodes from the "performer" content-type that you previously created. (FYI - the "third" table I describe above in the example is created for you by CCK - you don't need to create a third content-type.)

That should be enough to get you started.

Let us know how you did,

Shai

content2zero.com

content2zero.com

gordonbooker’s picture

gordonbooker commented

Thanks Shai - the node-reference field is just what I needed. Very kind of you to help me.
I can get the list looking pretty much as I wanted now.
I have the problem of node reference fields not being sortable, but from reading around on the forums it seems this is something extremely difficult for the developers to code.