Results 1 to 10 of 10
  1. #1
    Join Date
    Jul 2007
    Posts
    10

    videosample database with sample linked to one of many tables

    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!

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jul 2007
    Posts
    10
    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!

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jul 2007
    Posts
    10
    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:
    Code:
    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

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Jul 2007
    Posts
    10
    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.
    Last edited by CyberMonkey; 07-11-07 at 14:46.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by CyberMonkey
    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?
    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

    Quote Originally Posted by CyberMonkey
    edit: I just found this example of a database model for a product inventory, it looks like something we could use.
    um, that actually looks like an EAV solution (do a search on EAV/OTLT), and my advice is not to use it
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Jul 2007
    Posts
    10
    Ok thanks! I'll consult my good friend google on your keywords.

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by r937
    dude, nice diagram! that's how i design stuff, too
    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.
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •