help! trying to build a personal Film Database in Access
Rebecca Riordan
The two tables you're describing, films and series, are two types of the
same thing. Usually the best way to handle this is by using a technique
called "sub-classing". What you do is create a generic object that contains
all of the attributes that are common to both (or all, there can be more
than one) sub-classes, then create separate tables for each subclass that
contain the attributes specific to the subclass.
In your example, you might create a controlling table that contains the
FilmID, Title, etc. -- all the fields that are common to both types. Call
it, say, "Films". Then you would create a table called, say, "Features",
that contains only those fields that are specific to feature films, and
another table called, say, "Series" that contains the fields specific to
episodes of series. Both Features and Series would contain the FilmID from
the appropriate Films record, and would be related to the Films table in a
one-to-one relationship.
If you check Dev Ashish's site (www.mvps.org/access), there used to be a
sample of using subforms to handle this technique.
HTH
--
Rebecca Riordan
Author of Designing Relational Database Systems
http://mspress.microsoft.com/books/3222.htm
"Christian Brehm" <no...@nowhere.com> wrote in message
news:3a05db84...@news.tca.net...
> Hello-
>
> This is my first attempt at building a database and I would like your
> suggestions in building it.
>
> My two problems so far are this:
>
> 1) I had designed the database as follows: FilmID would be the key
> for every individual film. FilmID was tied to 2 tables:
>
> a. FILMS (normal studio films, etc)
> b. SERIES (shows that I have recorded, such as Seinfeld,
> Northern Exposure, etc.)
>
> 2) In reference to the SERIES table, I have indexed every episode,
> regardless if I have it or not. I would like all episodes to show up
> in a report regardless if they are duplicates or I do not have them
> yet.
>
> My questions are these: should I not have a separate table for both
> SERIES & FILMS? How should I design this database?
>
> I would post the actual database as is now, but I doubt it would be
> welcome since it would be considered "binary". Please offer any
> advice, suggestions, etc. I suppose this is a simple design question,
> but I have been going mad trying to work this out!
>
> Thanks again!
>
> Christian
>
>
Christian Brehm
Datamorphic
This sounds as if it would be at the discresion of the developer (you).
Certainly you could use only one table or you could use two. It depends on if
you see a major difference between a film and an episode of a series.
If you just want a listing of "shows" (a film or series episode) that you have,
I would go with only one table.
If you want to tie any episode shows to the series it belongs to (so that you
may be able to search for episodes for a particular series), I would suggest
having a second table of series with a one-to-many relationship to the shows
table.
Let me know if this helps.
Scott Machado
Datamorphic
Toll Free: 1-877-637-6912
Datam...@aol.com