I am designing a database that is going to store all the instruction videos / text / audio paths for using a device. The user can select whether they want the instructions to come in the form of video, text, or audio (can pick any of the 3, but has to pick at least 1). The user will also pick a language, this will be the language that the instructions come in. I think I have a few of the table structures figured out. However, how do I want to store all the combinations into one table?
For example, one of my tables has all the file path combinations,
path_id button_text audio_path video_path text_path
1 cbl.txt null null cbl.txt
2 cbl.txt null cbl.mp4 null
3 cbl.txt null cbl.mp4 cbl.txt
4 cbl.txt cbl.mp3 null null
5 cbl.txt cbl.mp3 null cbl.txt
6 cbl.txt cbl.mp3 cbl.mp4 null
7 cbl.txt cbl.mp3 cbl.mp4 cbl.txt
8 bolt.txt null null bolt.txt
9 bolt.txt null bolt.mp4 null
10 bolt.txt null bolt.mp4 bolt.txt
11 bolt.txt bolt.mp3 null null
12 bolt.txt bolt.mp3 null bolt.txt
13 bolt.txt bolt.mp3 bolt.mp4 null
14 bolt.txt bolt.mp3 bolt.mp4 bolt.txt
This takes into account all the different combinations, so based on the settings that the user picks, they will be returned the primary key corresponding to the right settings. However, I think this is too redundant and may not be very efficient, what is a better way to do it?
How about something like this:
device table:
device_id device_name
1 machine1
files table
file_id device_id file_type file_path
1 1 video d:\videos\v1.mp4
2 1 text d:\texts\t1.txt
3 1 pdf d:\pdfs\p1.pdf
...
Or if one file can be used for multiple devices, add another table:
device_file table:
device_id file_id
1 1
1 2
2 2
...
and you can remove the "device_id" column in your files table.