Database Structure Question

Hi,

As far as structure goes, I want to have a list of songs in Genre's on my website.

Lets call my database Songs
and a table for each genre (Techno, House, Progressive)

The structure for each table is:

ID, Artist, Song, Mix

Is that an Ok way of doing it instead of having one table structured like this:
ID, Artist, Song, Mix, Genre

If not, what are the downfalls?
LVL 3
Computer GuyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

GaryCommented:
Since genres and artists are unique and likely replicated then...

I would have one table that stores all genres
ID	Genre
1	Pop

Open in new window

Then one table that stores all Artists
ID	Artist
1	Adele

Open in new window

Then your song table would be
ID	Artist	Song			Mix	Genre
1	1	Someone like you	None	1

Open in new window

Artist 1 relates to the artist table which in this case is Adele
And the same for the genre
You could do the same for Mix's as well.
0
Dave BaldwinFixer of ProblemsCommented:
If you do it the second way with "ID, Artist, Song, Mix, Genre", you will only have to search one table to find a song title or artist.  If you use multiple tables for Genre's then any search will have to be run on multiple tables.
0
Computer GuyAuthor Commented:
Let me just revise for a second.

There will be a page for each genre.

So I was thinking of

Select * from Progressive for the code on the progressive page, as an example.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

GaryCommented:
Taking my example
Lets say Progressive has an ID of 2 in the Genre table then your sql would be like this

SELECT * FROM song_table INNER JOIN genres g ON g.id=genre WHERE g.id=2
0
Computer GuyAuthor Commented:
Do you know of any songlist managment apps?

I have something in Access that I would Export to a CSV and import into MySQL
0
Dave BaldwinFixer of ProblemsCommented:
Or...
Select * from Songs where genre = 'Progressive'
There are several ways to do this.
0
GaryCommented:
While Dave is right it might be easier to just use one table read up on database normalization

For example, it eliminates mistakes in repeating values - like entering Rock and Roll and then somewhere else you are using Rock 'n' Roll
0
Computer GuyAuthor Commented:
I have printable songlists in an Access Database in a nice report and want to display the list on my website.

I want to export them in Access and import them to my MySQL DB with HeidiSQL.

That way I can keep a uniform list in access and online.

If you have any other suggestions, I'd be open.

Sorry I wasn't clearer.
0
GaryCommented:
Not sure what you are asking for
Export from Access and import into MySQL?
0
Dave BaldwinFixer of ProblemsCommented:
You can use an ODBC connection to use the Access database as a front-end to the MySQL database so they stay synced.  I've only don the ODBC connection between them once or twice so I can't give you details.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.