Planning: DJ Catalogue

[KG52789]

Hey I'm new to these boards, and I'm here looking for some advice on a DJ Catalogue I'd like to create using FileMaker Pro 7.

I've got a decent-sized DJ business currently, and I'd like to be able to bring my laptop along to gigs and have a searchable FileMaker database of all our songs. This would help by showing us the exact track and/or disc of the requested song.

I read most of Jonathan Stars' "Learn FileMaker Pro 7" book, which has given me a pretty good understanding of general use of FM. I'm also very experienced with PHP and MySQL, which helps.

My main question is one on the design of the database. Here's the problem I'm facing:

Our DJ catalogue contains two different kinds of CDs. We have both full albums and mix CDs. (To clarify, a mix CD would be like "May 2006 - Top Hits" and a full album would be... a full album, all by the same artist.) I'm going to have to enter all of our current library initially, then add additional stuff as we get it, but I'd very much like to expedite the massive task of the initial input as much as possible.

I'd like to be able to either insert a new ALBUM (all tracks having the same artist) or a new DISC/MIX (where it would ask for song title AND artist for each song.) I know how to make multiple tabs for these two different input styles, but I'm unsure as to the best way to set up the tables. I definitely want to have a unique "serial/id number" for each disc (disc meaning the physical CD) and a seperate one for each track.

My initial idea was to have 3 tables: tracks, albums, and mixes... But then I wasn't sure which fields to link, or which pieces of information to have in each table. Just to be more specific, the fields I'd like to have accessible for every song are:

title, artist, album, disc (disc & album may or may not be the same), time, genre, ending (cold/fade), explicit (yes/no)

Sorry this turned into such a long post; I was just trying to provide enough information so that someone could easily respond. I look forward to your help.

Thanks in advance.

Kyle

Edited June 6, 2006 by Guest

[Virtus85]

Its confusing if you try to think of it all at once, but break it down like this:

Each artist can have many songs

so

Make a one to many from Artist --->Songs

each CD can have many songs

so

Make a one to many from CD's ---> Songs

Then put all the info about the song in the songs Table , put artist info in the artists table, then have your CD serial number in the CD table.

Don't worry about manually relating the Artists table to CD's one, filemaker knows they're already related by virtue of the fact that they're both associated with the songs table.

Hope that's what you were looking for

-another music fanatic coincidentally also named Kyle

(I just use iTunes )

Edited June 6, 2006 by Guest

[KG52789]

Hey thanks for the response. I sat down for a while and whipped out OmniGraffle (gotta love it) and drew up a diagram for what I was thinking about. How does this look? I included 2 examples, one of what the info would look like if adding an ALBUM and one if adding a MIX.

http://kyle.liquidfreaks.com/images/dj_catalogue_concept.jpg

Basically what I'd do is have two seperate input tabs. One would be for an ALBUM and the other for a MIX. The album input tab would ask you for the Artist and Album Name once, then it would just ask for a track listing (titles, times, etc.) The mix tab would ask for a Disc Name, and then each track would have a slot for Title/Artist/Album.

Do you forsee any problems with this?

Edit: I see your point about the artists, and like that idea... Could you help me figure out how to relate the three tables? Especially regarding the input process.

Edit2: FYI, I don't listen to Ciara :-P It was just something I found in one of my binders of DJ stuff.

Thanks again

Kyle

Edited June 7, 2006 by Guest

[T-Square]

Kyle--

It looks fine. Two things to consider--

1) Break out categories into a separate set of tables ("Categories" and "Track_Cats") so that you can assign more than one category to a track. It also affords greater control over the category terms (is it "Hip hop", or Hip-hop"?). Users can select categories from a value list.

2) Ditto for Artists.

HTH,

David

[KG52789]

Yeah I was planning on genre being selected from a menu to prevent the "hip-hop/hip hop" thing.

I *think* I understand what you mean with the artists, but I'm a little confused by your example of the "track_cats". Could you explain a little more how that would work? I hate to ask you to put a whole lot of work into this, but a diagram (like mine, just with your ideas) might be VERY helpful, if you wouldn't mind.

Thanks

[T-Square]

What I'm describing with Track_Cats is a join table. It's a means to allow you to have a many-to-many relationship (one track can have many categories, one category can have many tracks). The same ability to have more than one category for a track is mimicked with artists--i.e., you can have a track with more than one artist on it.

The join table Track_Cats establishes the concept that "Track A has Category 1". All it really requires in it is two fields, Track_ID and Cat_ID. These have to be the same format as their keyfield equivalents (i.e., if Tracks::Track_ID is a number, so is Track_Cats::Track_ID). With this table in place, the Category field in the Track table is unnecessary.

To add, edit and display a Track's categories, create a portal on the Track layout that uses the Track_Cats table as its source. Place the Track_Cats::Cat_ID on the portal as a dropdown list that uses a value list based on Categories. While you're at it, link the Track_Cats table in your relationship graph to a copy of the Categories table and put Categories::CategoryName on the portal as well. Make this read-only. (If you want, set it up as a button that uses the Go To Related Record command to open the Categories layout in a new window and showing only related records, which will allow your users to edit categories if they want) (Or don't do this here, but keep it in mind for the Artists portal...)

Everything I describe for the Track_Cats table applies to Artists. For example, the album Painted from Memory features Elvis Costello and Burt Bacharach. Any track you capture from this will have to reflect that fact.

HTH,

David

[KG52789]

Thanks, that actually helps a lot. Couple questions though.

If I'm understanding correctly, the track_cats table would house information about ALL aspects of a track, including artists, album, etc. Correct?

If so... How would adding albums and/or mixes to the database work? Would it first search to see if the artist exists, and then if not, add it to the artists table with a new "category id"? I guess I'm just confused as to what kind of information would go in each table.

The artist table would probably just be an ID number and an artist name. Would the albums/discs go in a seperate table? And then in the track table, there would be information such as ID, name, time, genre, explicit (yes/no) and soforth?

It makes sense to me to have more tables than just two (as I had originally stated, and indicated in my diagram). I'm pretty sure we're thinking along the same line here, but I was going to just give everything (artist, album, track, and maybe genre) a specific ID and then sort it out that way. (for a total of four tables, maybe?)

However, your method of the one "track_cats" table to store all connections sounds pretty nifty. Could you elaborate how exactly I'd input the information? Specifically along the lines of what I asked above - Would it first search to see if the artist already, and create a new ID if not?

Thanks for your help so far, and I look forward to your response.

Kyle

[T-Square]

No. Track_Cats ONLY joins a Track and a Category. The only fields in Track_Cats should be things that are attributes of a Track's category--the Track_ID and the Category_ID. I can't think of any other attributes in this instance, but there are times when you want other attributes in a join table, such as when you're selling something to someone, and you need to capture the sales amount of the something.

I'd put the Album_ID as a field in the Tracks table for simplicity's sake. I am recognizing that one track could appear on more than one album, for example a greatest hits album, but that in that case it would be more useful to duplicate the track info than to try and figure out how to have only one track record for Dancing Queen. I would then link this to the Albums table (but see below).

I'd have a separate Track_Artists join table with Track_ID and Artist_ID as the fields--just like the Track_Cats example.

Adding albums complicates the matter greatly. Specifically: You've got an album with an artist, so you want to enter the artist name just once. What happens when you start putting in tracks? Do you copy the artist each time? If you do, when you list all the songs on an album, how do you remove the duplicating artist entry? If you don't, then how do you know which tracks are by that artist, since the individual tracks lack an artist? It's all pretty annoying.

I will assume that, being a DJ, your primary purpose is to put your hands on the next great TRACK. Under that assumption, I'd downplay the album immensely because it'll get you into a mess. I might even go to the point of simply labelling and storing the albums numerically, and keeping no other info about them. Realistically speaking, that's all you need as a DJ--Dancing Queen is Disk 432, Track 7.

More later.

David

[KG52789]

I guess I'm a little confused as to what an example of a "track category" would be?

Also, I'm pretty sure we were thinking of different things when I talked about inputting data. I had planned on having 2 tabs, one for a new album, one for a new mix.

The album tab would look like:

http://kyle.liquidfreaks.com/images/dj_cat_concept2-1.jpg

And the mix tab would look like:

http://kyle.liquidfreaks.com/images/dj_cat_concept2-2.jpg

Very rough drawings (took about 30 seconds) but I think you get the idea. I'd still face the problem of checking to see if the artist was new (needed a new unique ID) or existed (already has an ID, store it with that one.)

I'm not *terribly* concerned with the album, I just thoguht it'd be nice to have.

But yes, you're right. Basically what I want is something where I can type in any information I know about a song (title, artist, album) and have it find the disc and track number for me.

Oh, and we will have duplicate songs FOR SURE (on multiple discs) so I was ready for that. There will just be two listings returned when I search, each with a different disc/track number.

So... I guess now I just need a little help figuring out what should go in each table, and how the data entry should work. I'm pretty good with the portals (we'll see...) but I still do have concerns about data entry.

Thanks

[T-Square]

A "track category" record is a database construct that allows you to assign an unlimited number of categories to a track, and vice versa. For example:

Tracks:

ID: 1

Title: Dancing Queen (Special Metal Mix)

Album: 1476

Track: 13

Duration: 13:37

Artists:

ID: 10 Name: ABBA

ID: 20 Name: Metallica

Categories:

ID: 100 Name: Disco

ID: 200 Name: Hair Metal

ID: 300 Name: Swedish Pop

Track_Cats:

Track_ID: 1 Cat_ID: 100 [this says Track 1 (called Dancing Queen ...) has Category 100 (called Disco)]

Track_ID: 1 Cat_ID: 200

Track_ID: 1 Cat_ID: 300

Track_Artists:

Track_ID: 1 Artist_ID: 10

Track_ID: 1 Artist_ID: 20

These records allow you to extract all:

Songs performed by Abba

Songs performed by Metallica

Songs with category Disco

Songs with category Hair Metal

Songs with category Swedish Pop

These searches would be carried out in their respective tables.

In each of these instances, the track with ID 1 will be retrieved, and you can tell that it is on album 1476, track 13, duration 13:37.

I'm not exactly sure what your drawings indicate, interface-wise, but I'll take some stabs here.

With your first image, you have to think about how you expect to handle adding artists to tracks. Since as a dj you're looking for individual tracks to play in a set, you have to have some way to either associate the artist info with each track record (which can be slow on input), or have some way to percolate the artist info down from the album to the track (which can be computer intensive and complicated to program). For example, if you create an album entry for Rubber Soul and put the Beatles in as the album artist, how will you search the tracks table to find all the tracks that the Beatles performed?

Now, turn it around: What do you do if you have the Best Disco Hits of 1976 with tracks by different artists? How will you capture the artist for a given track? I think the easiest (albeit not the prettiest) solution to this artist problem is to do the slower input process and associate the artist with the track and ignore the albums as much as possible. Thinking out loud, you might be able to find a way to script the creation of track-level artist records to speed up the input process.

Now in the Mix Tab window, you're implying another entity type--the Setlist. That'd be another join table opportunity.

You seem to indicate there that you intend to have your setlist window with input for Artist/Album/Time information. But if you've captured the track information, all this information will already be there. All your dj has to do is decide which song to run next. The stored track info will let her know what album it's on, what track it is, and how long it is.

In other words, separate the track capture function from the setlist function. Have an interface for capturing the contents of an album and its tracks. In this interface, you'll have the opportunity to add all the details about the individual tracks. Then, in a setlist window, allow the user to select songs for the set, keeping in mind that all the track detail will already have been captured. That will most likely have to be scripted to allow the user to search for songs that fit the set's profile. I'm imagining the DJ with Track 1, needing to find a suitable next song, and having to search by different criteria (categories, moods, tempos, themes, etc.) to get suggestions of what to put next.

Please realize that I'm not trying to sound like an annoying naysayer here! The world of sound recordings is hecka complex, and finding an intuitive interface to handle its complexities is difficult under the best of circumstances. I hope that my ideas will help you avoid some of the pitfalls I've encountered and build a truly outstanding dj database!

David

[KG52789]

First off, your examples of the different tables have helped IMMENSELY. Thank you very much. I think my confusion on the categories table stemmed from my misunderstanding of the word 'category' as you were using it. I would refer to that as a 'genre' of music, so I was confused what a category would be... So we were just using different terminology.

Well... I just typed about 4 paragraphs trying to explain how I would like to have two seperate inputs, blah blah blah... And then I realized my mistake. Not all tracks on an album have the same exact artist. Specific tracks might feature another artist, and I'd want that information in the database too. So yes, you're 100% correct. Artist information should just be filled out for each track seperately. (I can use copy/paste, shouldn't be a huge deal.)

However, I should still be safe asking for the Album Name at the very top of the "Add an Album" layout. All tracks on an album share the same album name obviously, so this should be a safe bet. When I actually hit Submit, it would input each track using the information provided for each one, and the album name provided at the top.

Is it feasible to not add one track at a time, and instead add one CD at a time? Because basically what I'd want to do is be able to have an album cover in my hand, and click "New Album." Then I'd like to be able to provide all the track information for that album on one page, not having to hit "New Record" (as in "database record", not vinyl) for each track. Does that make sense?

On the "Add a new mix" layout, I would like to have almost the same input as the "Add a new album" tab, but also require the user to input an album name for each new track. (This album name would be the album it came from originally.)

I apologize for my horrible attempt at drawing the different input methods in my previous post... (Hey, it made sense to ME...) : But I think you got the general idea. However, the second picture wasn't really a setlist, it was supposed to be the layout for adding a new mix CD as I described above. I don't really need a setlist function, but I will want a search function.

I think I've got a good grasp on database structure for this, so now I have a question about the actual use of the database. How hard will this search function be to set up? Can I just slap down the fields I want searchable on the "Find" layout, and type as much information as I know into there, and have it search? Or will I have to script it all? If I'm going to have to script it, how hard will that be? I'm very experienced with PHP/MySQL interactions, so that gives me at least some background, but I'm nowhere near an advanced coder when it comes to FileMaker.

Oh, and one more minor thing: Any reason why you used 100, 200, 300 for category and 10, 20, 30 for artist and 1, 2, 3 for track? I'm assuming it's just to make it easier to differentiate?

I really appreciate your help. Thanks for taking the time to write such detailed responses. It really helps.

Kyle

[KG52789]

So... I started playing with tables and such... And here's what I ended up with. Does this look close to what you were thinking?

I replaced "track" with "song" to eliminate confusion between track as in the number on the CD and the actual track itself, which is now called a song.

[T-Square]

Yup. That's about it.

[KG52789]

Sweet, makin' some progress!

If you get a chance, could you elaborate on how the FIND part will work? (see my post right before the one with the screenshot)

Thanks

[T-Square]

I would place a button that would take you to a search layout with global search fields for the kinds of things you want to search for (Artist, Title, Category, etc.). There, I'd have a search button that scripts the find and then displays the results in a pick-list layout, a list-type layout with a button that returns you the track ID. This approach yields trouble when you have a user who chooses to use FM's built-in search functions; they end up in places you hadn't planned...

But I think I'm working with a different model than you. I'm imagining using this database to create a list of songs for a set that you could save and use when you get to the floor. I'm not sure what it is you're looking to do.

David

[KG52789]

Ok, I think that was the general direction I was headed. But I'm not looking for a way to make a preset playlist so much as an "on-the-fly lookup" type thing. The DJ wouldn't be planning a setlist ahead of time. Instead they would just use the database to find (like you stated initially) what disc and track "Dancing Queen by Abba" is on.

[Aeriu]

And If i want to make a search album to see all the songs that are in it?? Like in itunes. You select the album and then you ahve a list of the songs...

How can i make that???

Thanks