Hello everybody,

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.

Anyone?

Thank you very much!

what is the realtionship between a sample and a codec?

i suspect each sample can be related to only one codec (disclaimer: i really don't know what a codec is)

therefore there should be a foreign key in the sample table which references the primary key of the codec table

you were trying to relate the codec table to the sample table, but you have to do it the other way round

Thank you very much for you quick answer! However, I think I didn't make myself clear enough. I made a little sketch trying to make it clear:

http://webs.hogent.be/~041294bm/misc/database.jpg

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?

Thank you!

dude, nice diagram! that's how i design stuff, too

you need a separate foreign key column in the samples table for each different codec table

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:

well, like i said, i would have a common table for all codecs

just like you wouldn't have a separate product table for every product in inventory

Yeah that would be the easiest solution indeed So we would have to try to find the cross-section of the most important parameters of every codec and only include those in our database?

edit: I just found this example of a database model for a product inventory, it looks like something we could use.

http://www.databaseanswers.org/data_...logs/index.htm

Thank you for the pointer on product databases, gives us an idea where to look for similar problems and solutions.

include them in the "master" codec table, which all samples will relate to, and put specific codec details in secondary tables

do a search on supertype/subtype

um, that actually looks like an EAV solution (do a search on EAV/OTLT), and my advice is not to use it

Ok thanks! I'll consult my good friend google on your keywords.

Me too. But it is missing a coffee ring and some not-quite-identifiable red sauce right over some critical text. As it stands I give it a 7 out of 10.

pootle flump ur codings are working excelent.