Closed Thread Icon

Topic awaiting preservation: sql Problem Pages that link to <a href="https://ozoneasylum.com/backlink?for=30596" title="Pages that link to Topic awaiting preservation: sql Problem" rel="nofollow" >Topic awaiting preservation: sql Problem\

 
Author Thread
Blacknight
Paranoid (IV) Inmate

From: INFRONT OF MY PC
Insane since: Dec 2001

posted posted 10-14-2008 18:20

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

twItch^
Maniac (V) Mad Scientist

From: Denver, CO, USA
Insane since: Aug 2000

posted posted 10-14-2008 20:01

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

Blacknight
Paranoid (IV) Inmate

From: INFRONT OF MY PC
Insane since: Dec 2001

posted posted 10-14-2008 20:08

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 ^^

DL-44
Lunatic (VI) Inmate

From: under the bed
Insane since: Feb 2000

posted posted 10-14-2008 21:29

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

Blacknight
Paranoid (IV) Inmate

From: INFRONT OF MY PC
Insane since: Dec 2001

posted posted 10-14-2008 23:43

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

DL-44
Lunatic (VI) Inmate

From: under the bed
Insane since: Feb 2000

posted posted 10-15-2008 00:17

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.

hyperbole
Paranoid (IV) Inmate

From: Madison, Indiana
Insane since: Aug 2000

posted posted 10-15-2008 16:49

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.

twItch^
Maniac (V) Mad Scientist

From: Denver, CO, USA
Insane since: Aug 2000

posted posted 10-15-2008 17:20

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

Blacknight
Paranoid (IV) Inmate

From: INFRONT OF MY PC
Insane since: Dec 2001

posted posted 10-15-2008 20:34

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)

« BackwardsOnwards »

Show Forum Drop Down Menu