Hey guys, first time poster to dbforums.

I am currently building a database for an online system but seeings this is my first fully designed db i'm having some teething problems.
I cannot figure out how I should create the tables/rows for this example:


I have user_table, which includes user_name, user_pass, user_email, etc.

I have music_genre, which includes just the primary key (genre_id) and the genre_name.


Now, I want my users to be able to have multiple genres assigned to their account. I understand i could restrict users to having, say, 3 genres, and then I just create 3 rows that relate to the genre_id's, but i dont want that restriction.

I figured i could make a row called user_genres and this could be a varchar that is delimiter seperated, allowing nth genres, but i dont think this is good practice, and would probably make any sql queries hell to work with.


Any help is much appreciated,
Regards,
Rhys

Just have these tables :

• User : user_id, user_name, user_pass, user_email, etc.
• MusicGenre : genre_id, genre_name
• UserMusicGenre : user_id, genre_id

Added an id field into user table.
Users can add as many genre rows as they wish.
You'll want to join the tables together to get the info you need.
Don't need user_ at start of all fields or genre_.
Add foreign keys to ensure the users / genres exist in UserMusicGenre.

Mike

so consider having an intersection table (which contains details of two, or more foreign keys)

eg
UserGenres
UserID
GenreID
you can have multiple genres for one user ID, multiple user id's for one genre.
so if you want to do a search on Genres you can qucikly find all users who have identified that genre.

you coudl extend the interscetion table to indacet a rating (could be 1..5 indicating dislike/like(, it coudl be a percentage), so you can identify people with similar likes and dislikes, you coudl identify people who may like genre blah, haven't expressed a preference on genre blugh but others who like blah like blugh

Thanks for the help, this seems like it will do the job, being the most logical. I have started adding user_ and genre_ for example to the start of every row because it seems to fix the issue with reserved words. (eg. I wanted a row called 'state' but i think thats reserved, so made it 'user_state')
Is this bad practice?


Thanks again, much appreciated.



Sorry, another question, I'm also trying to think up a solution so users can select multiple pre-defined genres, but also give them to possibility of entering their own. To do that i thought when they enter a new genre, its added to the genre list. But perhaps the genre table could have a 'genre_approved' type boolean row that defines it as default false. Therefore admin can check what genre has been entered and approve it to prevent any nasty's being entered and automatically visible on the site. Is this a good method?

adding the table name as a prefix is in my books a BAAAAD idea.
it has its uses when that column is foreign key in another table. so by all menas call it UserID in the foreign key, but call it ID in the user table.

however that is no ore than a naming convention, and there ae as many conventions as there are development departments.

new question new post... thats the normal forum ettiqutte

FWIW: the problem of allowing users to add their own genre's is that you can often find users have a different genre. I found it a problem when connecting to the gracenote music database. the same music may have different genres depending on whether its a UK, US, European or whatever CD/Album. it means there can be a proliferation of genre labels.


however there is a sneaky way of getting round this problem, and thats to have a genre transalation table, where you allow a user to describe their genres as whatever they like, and show them music that is that genre.. the sneaky trick is to have another table that identifies what user uses that genre and someone (probably you) then associates that genre with what your system knows.

so someone could define say the clash as 'old school punk', but your system defines it as punk. you then show any music defined as punk to that user as 'old school punk'. confused you bet.. its a lot of work, especially the associating their terminology with your terminology