[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 250
  • Last Modified:

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?
0
Computer Guy
Asked:
Computer Guy
  • 4
  • 3
  • 3
  • +1
2 Solutions
 
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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
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
 
Scott Fell, EE MVEDeveloperCommented:
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
  • 3
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now