Topic awaiting preservation: sql Problem

I have following Tables in my DB:

DVD:
id;title;.....

meta:
id;dvd_id; metaType_id; metacontent;

metaType;
id;Type

now in MetaType i store a list of available metra informations{"Genre", "Author",...}
in meta i store the according Information a Dataset would look like this {1;dvd_id; MetaType_id; "Fantasy"}

now here is the question how would i construct my sql statement to get ALL dvds where "Genre" matches "Fantasy" and "Author" matches "what ever"

any hints? best would be to create joins ..but i do not realy have a clue of them

thx

man, that's a scary freaking db scheme; meta content has no business in a database. Why not include that information directly in the DVD table? Though I'm having trouble figuring out who an 'author' of a DVD is. Gimme a couple rows from each table and I might be able to construct a SQL statement for it...

-S

thanks for the help but i already found out how. to be honest it is a scary db scheme. Why it is in the db ..hmm fast search and in a separate table so that i do not have to search all the text in the dvd table ..e.g searching for all dvds directed by "blabla"

ahh yes i meant director not author ^^

This kind of thing would normally be done with relational tables.

ie

Film
film_id, title

Director
director_id, name

Film_Director
director_id, film_id

etc...

FWIW

yes but this way i get to have as many "meta" information as i want e.g actors, genres, themes, story, locations, ...to have this flexebility would need A LOT of tables

A lot of tables, with a logical schema, and a meaningful structure, is still preferable to one big vague table holding everything...
This is the idea behind a "relational" database afterall.

And really, it's not that many tables. There are a handful of fairly standard data sets for this subject that would not be difficult to properly set up.

Your schema is called 'One True Lookup Table' You can find discussions of it on the InterNET by searching for that phrase and OTLT. Every person who is new to designing databases 'invents' it at some point. When I was trying to use my OTLTs, I found a lot of discussions and a couple of good articles discussing why it's a bad idea. Here's one of them. You might also look for discussions about OTLT at dBforums.

Take the advise of the people who have answered your question and abondon this idea. They've been designing databases for a long time and recognize it as a bad idea.

.



-- not necessarily stoned... just beautiful.

I recall working on a database that had a giant `meta` table that had two columns: `id` and `data`. The `data` column had PIPE-DELIMITED meta data, stored in TEXT datatype. Seriously. I had ulcers. Ulcers!

Then there's this other one, that had three columns, and held system settings data: `id`, `k` and `v`. The `k` values were A-Z, and the `k` values were... oh, you know, anything, really. For example, a `k` of 'C' could, say, have values in the `v` of '1 2 3 6' and where `k` = 'Z' maybe `v` is 'webmaster@this_site.com'

It was astonishing.

Bad database design doesn't just hurt you when you're trying to build queries for it, it hurts the people who have to come replace you years later and the company hasn't done anything to change.

Be the bigger man. Beat up the dwarf.

-S

Ok i do respect all of you so i will reconsider my db scheme.

And the more i think about it the more i dislike my first design.
I think i will go with n tables for n attributes and do my coding so that extending it to n+1 attributes becomes easy

thanks for the advise (also known as pointing me at my own S***t)

(Edited by Blacknight on 10-15-2008 20:34)