Mysql, Should I Make A New Table, Or Use Set Data Type?

[CalebCurry]

Let's say I have a database for music. it has an ID, the name of the song, and all that other information, and then it has style. (such as rock, rap, country...)

Would it be best to:

1. make a table of available styles and link it to the table with a foreign key,

2. make a table for styles and a table for music, and have an intermediary table listing all the styles for each song (to allow for multiple styles)

3. Use the SET or ENUM datatype to allow for specific choices.

 

Please explain why. Thank you!

[Antonio Conte]

It really depends on what you're after. Songs can definitely be in more that one music category, but most of the time, you can call it X. What really depends is what you like to do. One music genre per song or possibility of several genres per song?

 

Regarding your question, the first possibility to rule out is number 3. This is because an Enum is to hard to maintain during development. What defines a genre? Music is vague, and genres are often discussable. Some song even makes new genres. What then? It's definitely possible, but It's not a good solution in my mind.

 

Number 1 and 2 are better solutions. What you pick is really dependent on how you want your system to be. Number one only allows one genre per song. Number two allows several, but may be harder to write queries to. Once you got the query down though, it's really only a question of preference. (And they shouldn't be THAT hard to write.)

 

I would personally go for number two or number four, witch I will state here. Create a main genre per song. This genre will be stored inside the song table along other info. You then make both a genre table (you called them styles) and a song_genre table. (which will store song_id and genre_id) This way, you'll be able to display a main genre, several genres (including the one in the song table) altogether.

 

What you choose is really up to you. I think both solution one and two are good. Read my post and determine what your needs are first. Number fire (mine) is of course a combination of your solution one and two. Good job, btw. This table design sounds well planned.

[CalebCurry]

 

What you choose is really up to you. I think both solution one and two are good. Read my post and determine what your needs are first. Number fire (mine) is of course a combination of your solution one and two. Good job, btw. This table design sounds well planned.

 

I really need to know why ENUM or SET are bad options... Do you have the ability to explain farther why they are a bad choice? In the book I see Larry use it for male or female, but then again, those won't ever change, or anything.

For your 4th option, do you mean like this?

 

Song table - song ID, name, genre.

genre table - genre name

song genres table - Song ID, Genre ID.

 

to allow for a main genre and also some extras? if not can you clarify?

[CalebCurry]

I guess my main question is this, When should you use ENUM, and when should you Make more tables instead?? Thanks for the help so far.

[Antonio Conte]

When you should use enum? If you ask me, almost never. They can be great if you have a fixed number of items. A traffic light is therefor a good Enum. Gender, as you describe is also perfect. Another thing is cardinal directions. Enum stands for Enumeration. It means that every possibility inside an enum will have a number associated with it. It's basically a static, non-changing reference table internally. (enum_number, enum_choice) If your choices can ever change, enum is bad in my thoughts. Why do you want enums anyway? What's so great about them?

 

By alternative four, I was thinking this:

songs ( song_id, name, main_genre )

genres ( genre_id, name )

song_genres ( song_id*, genre_id* )

 

This makes "genres" a reference table. It's in my head a better solution than Enum because the choices are not fixed. A have taken several database courses in my programming education, so just trust me on it when I tell you it's the best solution. I can't really force you down this road, but it'll make your life easier in the long run... (This is "the correct way" if you look up normalization rules. Larry writes about it in some of his books. Maybe the one for this forum.)

 

... If you for any reason just HATE my solution, save genres as a JSON String or another CSV String in the songs table, then parse it with json_decode when using it. This will create an array in PHP you can manage normally. When you save again, use json_encode on the array to get a JSON String. It could be a viable solution as it's flexible, but I don't recommend it. If you want users to search songs by category f. eks, this might give you some problem. ("Rock&Roll", Rock and Roll, Rock 'n Roll", etc - Same category, different spelling)

 

Everything in table design is compromise and picking the solution that best suits your need. Take your pick, but think about the fallpits first if you don't choose "alternative four".

[Christopher Bergin]

really, what's so great about ENUM's???

[HartleySan]

Christopher, was that a rhetorical question or were you being serious?

I will trust you were joking, since Antonio already very well explained the uses and drawbacks of ENUM above.

 

Just to add a little discussion to the mix, what about the artists? Don't you care about the song artists?

Just to give you an idea of all the metadata that's relevant to a given song, open iTunes, right-click on a song, select Get Info, select the Info tab and look at all the info associated with a given song. There's a lot.

Not sure how far you want to take this project, but if you're serious about it, it's best to consider as many things as possible now and design your DB to be robust from the start.

As Antonio stated, a good design from the start will save you a million headaches later.

[Antonio Conte]

I think that fits under "all that other info", Jon. That's how I understood it. I agree, though. Look at Spotify/iTunes etc so you don't miss anything, TS. It's really alot you can chose to save. Albums, track number, related artist, similar albums, song ratings. Pretty endless.

[CalebCurry]

Thank you so much everybody, I'd like to let you know that you helped me a lot. I understand what you mean with the main genre, and also why enum is not recommended. THANKS!