CODEWITHSUNDEEP
database-designforeign-keysforeign-key-relationship
Christopher
Christopher

Reputation: 63

Acceptable foreign key use

I am creating a database for my multimedia collection and I have found myself with a doubt of my design and it makes me wonder if there is a more efficient way to achieve what I want.

I am trying to design the database in a hierarchical manner. I won't include it all but enough.

The main parent table is called MONOLITH. It has BLOCK_ID and BLOCK_NAME as attributes.
The rows are:

1 , "INDEX"

2 , "LIBRARY"

Then the two child tables are INDEX and LIBRARY. The former for movies and the latter for TV shows.

Where I'm starting to doubt the integrity of the database is that the data for movies and TV shows is different and the same attributes aren't needed for both so I think they should be in different tables. So in the INDEX table every rows foreign key is 1 and in the LIBRARY table every rows foreign key is 2. Is there anything explicitly wrong with this or is there a different way to achieve what I want? This is only a small example, there is also music, text, audio books, and installation files as well which will all be children on the MONOLITH table. Every one of them will have the same foreign key for every row as well respectively.

Any help would be greatly appreciated, thanks!

Upvotes: 1

Views: 55

Answers (2)

TAM
TAM

Reputation: 1741

Your observation that the links from the tables carrying the actual information about music, text, etc., to the monolith table would be redundant. As far as your description goes, that monolith table is superfluous, as it doesn't carry any information that can't be drawn from those other tables. You don't need to implement the list of contents of your database. Any query pertaining to music, text, video, or whatever, should directly go to the tables containing them. If you need indexes, define them within those tables.

Your wish to get all movies, music pieces etc. by title doesn't need that monolith table either. You can achieve that by something like

select id, name, "movie"
from movies
where name like "Singing%"

union

select id, name, "music"
from music
where name like "Singing%"

Altogether, your monolith approach (which is rather unusual) appears like a custom implementation of things that can be left to the database, like maintaining indexes.

Your file subfolder remark points to the direction that you want to have a genre system for your media pieces. In a database, the best way to express this is not via separate tables, but by first having a table

Genre(id, name, supergenre_id)

that can be used to express a genre hierarchy like

1   Pop        null
2   Brit-Pop   1
3   Italo-Pop  1
4   Classical  null
5   Baroque    4
...

and then just have a genre_id attribut with each medium. You are much more flexible with a database than you would be with a file system. The folder structure only allows to express one hierarchy, whereas with this kind of database scheme you may have many of them, e.g. mood, instrumentation, etc., in addition to genre. That's what makes a database fun.

Upvotes: 1

phonetic_man
phonetic_man

Reputation: 1088

The monolith table does not serve any purpose. As you have described in your problem statement each row of the child table will have the exact same value for the foreign key column, this is not what foreign keys are meant for. To meet your requirements you could define a view which contain all the common attributes across this child tables.

CREATE OR REPLACE VIEW
multimedia_collection
AS
SELECT id, name, attribute_1, attribute_2, 'MOVIES' AS media_type
FROM movies 
UNION ALL
SELECT id, name, attribute_1, attribute_2, 'MUSIC' AS media_type 
FROM music
UNION ALL
....
....

Now, you can fire different queries against this view. For example to find all the multimedia items which contain the name STAR WARS you can fire the following query.

SELECT * FROM multimedia_collection
WHERE name LIKE 'STAR WARS%'

To find the count of each multimedia type you can fire the following query.

SELECT media_type, count(*) FROM multimedia_collection
GROUP BY media_type

These are just some examples you can do much more once you define the views.

Upvotes: 1

Related Questions