Advice on design relations between tables

Question

I have information about music albums that I want to organise in RDBMS tables with relations between them. I have the following info for each album: artist, album name, year, label, genre, style, rating. So far I think to make 4 tables - artists, albums (name, year, label, rating), genre1 and genre2 (each genre with its styles). On the diagram it looks as follows:

But don't know yet how can I establish a connection between albums and the other three tables? I.e., when I will run a query select name from artists I would like to receive an album with corresponding artist and genre-style.

How should I establish a relation between the tables in this case?

Silverstein

Answer 1

You need to read an introduction to relational database tables & querying.

The "relations" [sic] between tables that you are talking about are FKs (foreign keys). A FK says values for a list of columns in a table appear as values for some other list of columns in a table that form a PK (primary key) or UNIQUE set there. You don't need to declare or use FKs to query. Like all constraints, including PK & UNIQUE, they are for the DBMS to exclude erroneous database states.

A (base or query result) table represents a (business/application) relation(ship)/association. A table holds the rows that make some true proposition (statement) from an associated predicate (statement template). The predicate of a base table is given by the DBA. The predicate of a query result table follows from the base tables, relation operators & logic operators in the user's query expression. Ie the predicate of a JOIN is the AND of its tables' predicates; UNION the OR; EXCEPT the AND NOT; ON and WHERE of a condition AND that condition in with the JOIN predicate; etc.

-- artist A has name N
Artist(A, N)
-- album A has name N and ...
Album(A, N, ...)
-- genre G has name N
Genre(G, N)
-- artist A authored album A2
ArtistAlbum(A, A2)
-- album A is of genre G
AlbumGenre(A, G)

SELECT DISTINCT ...
FROM
    --     album ag.A is of genre ag.G AND genre g.G has name g.N ...
    -- AND ag.G = g.G ...
    AlbumGenre ag JOIN Genre g ON a.G = g.G ...

Notice that it does not matter how many genres an album can have or how many albums a genre can have or whether a genre can have multiple ids and/or names, the query still returns the rows that satisfy that predicate. Constraints (including FKs) are not needed to query or update.

Notice that we can apply the same predicate transforms plus others to write constraints. (I used A for both authors & albums so I'd have to give a renaming example here.)

-- for all A & A2, if artist A authored album A2 then artist A has some name
-- for all A & A2, if artist A authored album A2 then for some N, artist A has name N
-- for all A & A2, if (A, A2) in ArtistAlbum then for some N, Artist(A, N)
-- SELECT A FROM ArtistAlbum ⊆ SELECT A FROM Artist
FOREIGN KEY ArtistAlbum (A) REFERENCES Artist (A)

-- for all A & A2, if artist A authored album A2 then album A2 has some name
-- for all A & A2, if artist A authored album A2 then for some N, ..., album A2 has name N and ...
-- for all A & A2, if (A, A2) in ArtistAlbum then for some N, ..., (A2, N, ...) in Album
-- SELECT A2 FROM ArtistAlbum ⊆ SELECT A AS A2 FROM Album
FOREIGN KEY ArtistAlbum (A2) REFERENCES Album (A)

-- for all A & G, if album A is of genre G then album A has some name and ...
-- for all A & G, if album A is of genre G then for some N, ..., album A has name N and ...
-- for all A & G, if (A, G) in AlbumGenre then for some N, ..., (A, N, ...) in Album
-- SELECT G FROM AlbumGenre ⊆ SELECT A FROM Album
FOREIGN KEY AlbumGenre (A) REFERENCES Album (A)

-- for all A & G, if album A is of genre G then genre G has some name
-- for all A & G, if album A is of genre G then for some N, genre G has name N
-- for all A & G, if (A, G) in AlbumGenre then for some N, (G, N) in Genre
-- SELECT G FROM ArtistAlbum ⊆ SELECT G FROM Genre
FOREIGN KEY AlbumGenre (G) REFERENCES Genre (G)

Instead of having two tables Album & AlbumGenre and their FK we could have just Album2 that is their join, with predicate that is the AND/conjunction of their predicates album A has name N and ... and album A is of genre G with FOREIGN KEY Album2 (G) REFERENCES Genre (G). Then normalization would tell us that if there is one genre per album then that's an OK design but otherwise that the original is better. Similarly for Artist2 combining ArtistAlbum into Artist (reasonable if an artist authours one album). Or both ArtistAlbum & AlbumGenre into Album3 (reasonable if an album has one author and one genre). But regardless all that matters to query & update are the predicates, not the cardinalities or constraints.

So your design is missing appropriate predicates/columns/tables like those of ArtistAlbum & AlbumGenre. (Which you might want to combine with other tables as above.)

PS Your question is not clear about "genre", "genre1" & "genre2".

Crossway

Answer 2

The bottom line is that you need foreign keys. Your tables currently have a distinct id for each table named id:

artist.id
artist.name
album.id
album.album
album.year
album.label
album.rating
genre.id
genre.name
genre.id
genre.name

Key word is "relational" here. You need to relate the tables. Perhaps you'd design this by naming your ids better:

artist.artist_id
album.album_id
genre.genre_id

Then in the album table, you will add columns for artist_id and genre_id so you can join them back to the artist and genre tables

Without FKs, you will have a Cartesian product. Simple as that.

Arette

And then in album table, I need to add a column artist_id and genre_id that will point to respective rows in artist and genre tables? I thought about two genre tables because one will be for rock and another for jazz. – Silverstein

I edited my post to address your first question. Regarding the genres, you are defeating the purpose of a table altogether in creating 2 genre tables. Jazz is one row with its own key, Rock is another row with its own key. It's the same concept as why you don't build a table for each artist, or a table for each album. – Arette

@VitaliiPlagov & fleetmack PKs/UNIQUEs & FKs are not needed to query. Eg as long as a join is ON a condition there will not be a cross product, ie there doesn't have to be a PK/UNIQUE or FK involved, ie one column doesn't have to be a subset of another column that is a PK/UNIQUE (ie a FK) and it doesn't need to be compared to its PK/UNIQUE. See my answer. – Crossway