videosample database with sample linked to one of many tables
I'm working with a friend on an assignment for school. It consists of the design of a database which can be used to store videosamples and especially a lot of information about them. There are a lot of videocodecs which all have their own property set for quality and encoding parameters. It also needs to be easily upgradeable with new codecs.
So we have a main table "samples" where every sample has his own ID. But then we ran into problems. If we have a table for codec1, codec2 and codec3 how do I make clear that a specific row in one of those tables is connected to a sample? I am familiar with the concept of association table however that doesn't seem to be the solution in our case, since there are many different tables.
We thought about storing the tablenames but that seems like a bad design and we doubt if it's possible to use the result of a query as a tablename (as this reply indicates it isn't...)
We are sure there must be some elegant solution to this (and that a lot of people have ran into this issue), but its not an easy thing to google for.
so I want to store specific data about every sample in the database, but for every codec (you are right that every sample has one codec) there are different parameters.
So in the example, sample 1 has all the data of the "samples" table, and all the data in the corresponding row of "MPEG-1". Sample 3 has all the data in the samples table, and the data of the corresponding row in table "WM9".
My question is: how do I link the row in the "samples" table with a row in one of the codec-tables?
Heh, thanks for the compliment on my drawing skills ;-)
Yours can be a solution indeed, however it seems to be quite a hassle to add new codecs later on, and to design queries? Also, I would need to create an ID in the codec-tables, where in fact every sample already has an ID, in the samples-table.
A query would look something like this:
select * from samples a
left join WM9 b
on a.fkwm9 = b.id
left join MPEG1 c
on a.fkmpeg1 = c.id
left join codecx c
... and so on for every codec (could be 20-30...)
where a.id = 3