CODEWITHSUNDEEP
androidsqldatabasesqlite
Eng.Fouad
Eng.Fouad

Reputation: 117589

SQLite column with multiple values

I have 3 tables in SQLite database:

Songs:

_id | name | length | artist_id | album_id

Artists:

_id | name

Albums:

_id | name

I need artist_id in Songs table to have the ability to have multiple values. What is the best way to accomplish that?

1- Make it as a string field and the value would be something like: 1, 2, 5, where each number represents an id from Artists table.

2- Create a table for each record in Songs table, where each table would have the multiple values (seems very bad solution?!).

3- Create a table with the following fields:

_id | artist_id

where _id represents _id in Songs table, and it is not primary key. While artist_id is the id of the artist.

Second part of the question:

If I create the 3 tables correctly, how can I write a query to have the following table:

_id | name | length | artist1_id | artist1_name | artist2_id | artist2_name | ...

where I got all the artists of a song in a single table (though most of the songs have only one artist and some have none).

Upvotes: 1

Views: 5788

Answers (4)

Damian Leszczyński - Vash
Damian Leszczyński - Vash

Reputation: 30865

The case is that you have a one Artist can write many songs and one Song can be written by many artists.

This conclude to association many-to-many. Typically this type of relation are represented by additional table that store the relation in your case would be

Artist_Song:

artist_id | song_id

Then you should remove that column form Artist table. Then the database would be normalized in 3NF.

This approach is not rule of thumb, sometimes it is changed because of additional workload required for database to resolve the data.

As far it goes for the second question, that depend of DB schema. If you use above structure then you will have to join tables to have all required information, but those will be presented in rows, then you should pivot to represent artists in columns.

BTW. Please do not forget that a song can appear in different albums.

Upvotes: 3

AggelosK
AggelosK

Reputation: 4341

For the first part of your question i would suggest your 3rd proposal as the way to follow. You can skip the table creation (if you want) by simply getting your songs by excecuting this query:

select Songs.* 
from Songs, Artists
where Songs.id = Artists.id and Artists.id = $the_id_of_the_artist_whose_songs_you_want_to_get$

For the second part of your question, i don't know if you can do what you suggest but here is a similar but not exact solution. You can create a view using this query:

create view your_view as
select songs.id, songs.name, songs.length, artists.id, artists.name
from songs
left outer join artists
on songs.id = artists.id
where songs.id = &the_id_of_the_song_you_want&

However, in my opinion, you can just use a query to get the results you want like this:

select songs.id, songs.name, songs.length, artists.id, artists.name
from songs
left outer join artists
on songs.id = artists.id
where songs.id = &the_id_of_the_song_you_want&

The above will returns multiple rows for each artist in a song but not multiple columns.

Upvotes: 0

duffy356
duffy356

Reputation: 3718

Why do you have the same id for Artist, Album and Songs?

if you make artist_id and album_id foreign key, you would have a solution.

Upvotes: 0

10s
10s

Reputation: 1699

1 question: artist_id in songs. Typical master-detail table

2 question: I guess the easiest way is to return a dataset that looks like the below, is easier in parsing and much easier though dynamically creating columns is difficult and the sqlite is not that cooperative in doing such things:

song_id | artist_id

the above dataset should have duplicate entries because an artist has more than one song and in that way with a simple order by artist_name you could order them very neatly. Then for all the necessary songs just make a second select

select [fields] from songs where song_id in (1,2,3,4,...);

this will return a new dataset that has all the necessary information of the song.

Upvotes: 0

Related Questions