Is this a bad database design?

I have a DVD database. I have several categories (three) that a DVD
may be in.
I put all three possible topic fields in the main table that holds the
title, etc. of the DVD.
For example, I have:
title, when_made, running_time, genre, subject, specific_subject
the last three are the three types a DVD may be.
My question is: Should I put the genre and subtypes all in that main
table or should I have related them in some way to the main table?
Thanks for any help.There should be a Genre table with a primary key and the Genre name. Then,
you set up a foreign key from the DVD table to the Genre table. If there
can be more than one genre per DVD, then you'd need to have a third table -
known as a link table or associative object - in order to resolve the M:M
relationship. In that case, there would be 2 foreign keys from it - one to
the Genre and another to the DVD. There would be no FK from DVD to Genre.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
<needin4mation@.gmail.com> wrote in message
news:1128428580.337372.277460@.g43g2000cwa.googlegroups.com...
I have a DVD database. I have several categories (three) that a DVD
may be in.
I put all three possible topic fields in the main table that holds the
title, etc. of the DVD.
For example, I have:
title, when_made, running_time, genre, subject, specific_subject
the last three are the three types a DVD may be.
My question is: Should I put the genre and subtypes all in that main
table or should I have related them in some way to the main table?
Thanks for any help.|||I keep the foreign keys in the main table. I have three other tables:
genre, subgenre, and subsubgenre (okay they aren't really called
subsub, but you get the drift). Each genre and sub has a key in the
main table. I don't save the wording, just the key. I was thinking
that having main genre, subgenre, and subsubgenre like that, all three,
in the same main table (even though keyed) was "wrong."|||Then it sounds like all you need in the DVD table is the subsubgenre, as far
as FK's are concerned. The subsubgenre should have a FK to the subgenre
table, which in turn has a FK to the genre table.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
<needin4mation@.gmail.com> wrote in message
news:1128439915.363058.318680@.z14g2000cwz.googlegroups.com...
I keep the foreign keys in the main table. I have three other tables:
genre, subgenre, and subsubgenre (okay they aren't really called
subsub, but you get the drift). Each genre and sub has a key in the
main table. I don't save the wording, just the key. I was thinking
that having main genre, subgenre, and subsubgenre like that, all three,
in the same main table (even though keyed) was "wrong."|||To be honest, it sounds like you need a many-to-many relationship
dvds - dvdGenres - Genres
This way you can assign multiple Genres to a single dvd without limiting
yourself to X number of them.
<needin4mation@.gmail.com> wrote in message
news:1128439915.363058.318680@.z14g2000cwz.googlegroups.com...
> I keep the foreign keys in the main table. I have three other tables:
> genre, subgenre, and subsubgenre (okay they aren't really called
> subsub, but you get the drift). Each genre and sub has a key in the
> main table. I don't save the wording, just the key. I was thinking
> that having main genre, subgenre, and subsubgenre like that, all three,
> in the same main table (even though keyed) was "wrong."
>|||>> Should I put the genre and subtypes all in that main table or should I
If they are distinct entity types, you must represent them in separate
tables. Based on your narratives :
CREATE TABLE Genre ( genre PK, ... )
CREATE TABLE Subjects ( subject PK, genre FK, ... )
CREATE TABLE Specifics ( spec_subject PK, subject FK, ... )
CREATE TABLE Movies (
movie_id PK, title, production_date, running_time, spec_subject FK,
UNIQUE( ... ), CHECK ( ... ), )
A seemingly easy & common, but misguided design is to cram all of them up in
a single table with NULL-able columns.
Anith|||I understood up until you had spec_subject FK in the Movies table. I
would have thought that genre went there. Do I misunderstand?
I actually have this design (except for the movies part), but was going
to use it for a drop down list. If for example a person selects
"Horror" they would not have the subject "kids under three movie"
(hopefully) and therefore should not have that in the selection.
In the main table that actually holds the data, I had the three columns
and insert a genre, subgenre and subsubwhatever in the table. The
values were limited at the data entry.
But what I think you are all saying is that if I have a single column
for genre that that would have a foreign key in subjects and that when
I retrieved my records it would pull that movie, that single genre and
then all subjects under that genre. Somewhere someone would have to,
of course, type in what subject was under a certain genre, but that is
okay.
On the other hand I'm not sure if that works because a Genre may imply
many subjects, but not all subjects apply to the save movie. It may be
comedy and have slapstick, black comedy, and so on, but the movie
itself may only be comedy and slapstick.
So how would I store this? Are you saying that in my table that I
would have:
movie - movieid, title...
genre - genreid, genre_verbiage
subject - subjectid, genreid, subject_verbiage
specific - specificid, subjectid, genreid, specific_verbiage
Is this why are saying to have the spec_subject FK in the master (one
side) table? so that when retrieve the data it would be like this:
select * from movie, specific, subject, genre
where
movie.spec_subject = specific.spec_subject
and
specific.subject_id = subject.subject_id
and
subject.genreid = genre.genre_id
Sorry for the long post. Just trying to understand. I inherited the
database.|||In general, newsgroups are not a great place for design advice since your
conceptual model & business rules are mostly transparent to others here.
Miscommunication and misinterpretations are common and the suggestions one
receives here are based on what others perceive as the problem and could
possibly be wrong with regard to the actual problem.
My lack of familiarity to your conceptual model and business rules might
have contributed to the misunderstanding. Your initial post gave me the
impression that specific subjects belong to subjects and subjects in turn
belong to genres. In other words, in my mind, a movie falls under one
specific subject, which in turn belonged to one main subject that belonged
to a single genre.
Before thinking about client side interface controls, let us consider the
actual entities, their attributes and the relationship among them. In your
case, is there a relationship between subjects and genre? Is there a
relationship between specifics and subjects? Can you post some examples for
each?
As a general recommendation, integrity constraints should be applied at the
database as well rather than only at the data entry interface.
Here is the categorization at blockbuster online: http://tinyurl.com/8rgz9 .
Do you have something similar? If not, post some examples for genre,
subject, specific_subject etc.
The general design rules of thumb are:
* When you have a 1-to-1 relationship between two entity types, unless there
are any non-de preserving relationships, you may represent them in a single
table.
* When you have a m-to-1 relationship between two entity types, you should
use a referential integrity constraint ( FK ) between the tables
representing these entity types
* When you have an m-to-n relationship between two or more entity types, you
should introduce an "association" table which reduces the schema to two or
more many-to-one relationships on each table representing these entity
types.
Anith|||>> I was thinking that having main genre, subgenre, and subsubgenre like tha
t, all three, in the same main table (even though keyed) was "wrong." <<
No, not if they are really different attributes. However, I would
prefer to design a hierachical encoding like Dewey Decimal for this
kind of thing.