Designing a simple access Database for a friend

[leeland]

Hello,

This is my first time actually making a DB in access...I know it is pretty easy and all but I was hoping to make it nice for this friend of mine.

He has over 750 movies, DVDs and VHS...he lives in the country without cable tv 🙂

Anyways he wanted to have a DB designed so he could import all the movies and then be able to search off of the DB for a specific movie and make sure he has it and so forth.

So I designed a single table with easy fields

index the Pk
format VHS or DVD
Title just text
Sequel True/False
Genre Action, disney...ect
Rating 0 - 100


So I have a question...I was thinking about breaking this up into a couple tables.

I was going to have a genre table that was linked with the main table...I don't know if that is even a good idea or not so that was one of my questions...I think that he wants to have a form that he can search off of to bring back a list of movies....

For instance

Bring back all Disney movies...
Bring back all DVD's
Bring back all sequels...


I haven't added the sequels field yet, it was something I was toying with in my head.



My question is...

How hard is it to make a nice fully functional form that he can search off of and so forth ? Is that something that requires some programming within access or is it pretty simple. Can I use like radio buttons or check boxes so he can use that for his searches and so forth ?

If anyone knows of any decent websites that might be of help I would appreciate it as well

Like I said I have never done this before and thought it would be a cool project and at the same time helping him out.


Thanks for any advice...


Leeland

 

[KB]

What you plan to do wil be very easy in access. It won't take much programming since Access has alot of Wizards which will guide you through that stuff. But you will have to do some programming so you might as well try to learn some VBA.

I haven't found any good websites for Access, but I do have the SyBex Access 2000 developers edition book and have liked it.

Using a 'lookup" table for Genre is a good idea as it saves storage space and allows you to rename the genre if you wish. You might also create a lookup table for Format:
1 DVD
2 VHS
3 HDDVD

 

[torpid]

Hard to say whether you'd find form programming difficult. For a career programmer it's not hard at all.

I would definitely not name a column "index", nor would I have a string column for the format. What happens if you typo DVD and make it DBD?

If you want to go with the more common approach, you should move genre out into a separate table entirely, and make it so a movie can have multiple genres. I think that's what you were asking.

I don't know what use the sequel field is. It will be more useful to track which movies are from the same series, again in another table. Just knowing something is a sequel wouldn't help find the movie that came before it necessarily.

I'd also suggest a year due to title duplication and remakes. e.g. maybe he owns both versions of Sabrina or Ocean's 11

 

[leeland]

Originally posted by: torpid
Hard to say whether you'd find form programming difficult. For a career programmer it's not hard at all.

I would definitely not name a column "index", nor would I have a string column for the format. What happens if you typo DVD and make it DBD?

If you want to go with the more common approach, you should move genre out into a separate table entirely, and make it so a movie can have multiple genres. I think that's what you were asking.

I don't know what use the sequel field is. It will be more useful to track which movies are from the same series, again in another table. Just knowing something is a sequel wouldn't help find the movie that came before it necessarily.

I'd also suggest a year due to title duplication and remakes. e.g. maybe he owns both versions of Sabrina or Ocean's 11

Thanks for the input,

The field was not index, it is movie index...I must have forgotten to type that in. Also, I have a rule on the format field...it can only be DVD or VHS...so if they misspell it they get a validation error...

I did think about the year field, but this guy has like 750 movies and there is no way in hell that I am going to go and look up all those movies.

So if I move the genre out of the table....and place it in a seperate table like this

Genre Index (Pk)
Genre

Then how would I link that field to the movie table ? Would I have to make a Genre field on the movie DB and make it a number ?

I think I tried that once and it gave me an error saying something along the lines that it can't link a PK and a number field...

so if I did get it to link would I just have to put in a number into that field within the movie DB ? so that it would link up with the new Genre DB ?

so it would be somthing like this

1 - action
2 - horror
3 - western
4 - disney
and so on...so that in the movie DB you would not have westren in the genre field, but you would actually just have a number 3 ?? is that right ?

So if I made this in visual basics.net it shouldn't be that hard to make a form to connect with the DB do you ?


Thanks for the replies


Leeland

 

[EagleKeeper]

The main table will have indexes corresponding to the subtables.

You can either prepare queries for each type or have the Form build the query(if possibleusing wizards) or put a small amount of VBA code behind the form to analyze the form options and create the query accordingly.

 

[leeland]

Originally posted by: EagleKeeper
The main table will have indexes corresponding to the subtables.

You can either prepare queries for each type or have the Form build the query(if possibleusing wizards) or put a small amount of VBA code behind the form to analyze the form options and create the query accordingly.

That makes sense that all the fields in the Movie DB will be indexes that link to other fields

so for example a record in the movie DB would like like so

movie index format title genre rating
1 0 Spiderman 4 90


So then there would be a total of 3 tables

the Movies table
the Format table
the Genre table


does that sound right ?

do the fields that are in the movie table have to be primary keys ???


so if I want to match up the format field(which would be a 1 or 0) in the movie table and the format index(which would be the PK) in the format table...does that link work ? Format(num) ----format index(Pk) Or will I get an error ?


Thanks for the help guys

Leeland

 

[EagleKeeper]

Originally posted by: leeland
do the fields that are in the movie table have to be primary keys ???


so if I want to match up the format field(which would be a 1 or 0) in the movie table and the format index(which would be the PK) in the format table...does that link work ? Format(num) ----format index(Pk) Or will I get an error ?


Thanks for the help guys

Leeland

The index field in the sub-tables should be primary keys.
The corresponding fields in the main table should just be tagged as indexed.

As to the actual programming of the query, you will be on your own.
I have not played around with Access forms since 2000

 

[leeland]

Originally posted by: EagleKeeper

Originally posted by: leeland
do the fields that are in the movie table have to be primary keys ???


so if I want to match up the format field(which would be a 1 or 0) in the movie table and the format index(which would be the PK) in the format table...does that link work ? Format(num) ----format index(Pk) Or will I get an error ?


Thanks for the help guys

Leeland

The index field in the sub-tables should be primary keys.
The corresponding fields in the main table should just be tagged as indexed.

As to the actual programming of the query, you will be on your own.
I have not played around with Access forms since 2000

When you tag a field in a main table as indexed, what does that do ? and why do you need to do that ?

thanks,

Leeland

 

[torpid]

Originally posted by: leeland

Originally posted by: torpid
Hard to say whether you'd find form programming difficult. For a career programmer it's not hard at all.

I would definitely not name a column "index", nor would I have a string column for the format. What happens if you typo DVD and make it DBD?

If you want to go with the more common approach, you should move genre out into a separate table entirely, and make it so a movie can have multiple genres. I think that's what you were asking.

I don't know what use the sequel field is. It will be more useful to track which movies are from the same series, again in another table. Just knowing something is a sequel wouldn't help find the movie that came before it necessarily.

I'd also suggest a year due to title duplication and remakes. e.g. maybe he owns both versions of Sabrina or Ocean's 11

Thanks for the input,

The field was not index, it is movie index...I must have forgotten to type that in. Also, I have a rule on the format field...it can only be DVD or VHS...so if they misspell it they get a validation error...

I did think about the year field, but this guy has like 750 movies and there is no way in hell that I am going to go and look up all those movies.

So if I move the genre out of the table....and place it in a seperate table like this

Genre Index (Pk)
Genre

Then how would I link that field to the movie table ? Would I have to make a Genre field on the movie DB and make it a number ?

I think I tried that once and it gave me an error saying something along the lines that it can't link a PK and a number field...

so if I did get it to link would I just have to put in a number into that field within the movie DB ? so that it would link up with the new Genre DB ?

so it would be somthing like this

1 - action
2 - horror
3 - western
4 - disney
and so on...so that in the movie DB you would not have westren in the genre field, but you would actually just have a number 3 ?? is that right ?

So if I made this in visual basics.net it shouldn't be that hard to make a form to connect with the DB do you ?


Thanks for the replies


Leeland

Just steer away from the term index in general. If you keep calling them indexes now, when you get deeper into DBs it's going to mess with your head, because an index is actually something else. You can go with MovieID.

Anyway here's the type of structure I meant:

Format
--------
[pk] FormatID - Autonumber
FormatDescription

Movie
-------
[pk] MovieID - Autonumber
FormatID - number
Title
Rating

Genre
-------
[pk] GenreID - Autonumber
GenreDescription

MovieGenre
-------------
[pk] MovieID - Number
[pk] GenreID - Number

Series
-------
[pk] SeriesID - Autonumber
SeriesDescription

MovieSeries
--------------
[pk] MovieID - Number
[pk] SeriesID - Number
(maybe)SeriesOrder (which order in the series the movie is - you might make this and series id the key instead of movieid/seriesid)

 

[torpid]

Originally posted by: leeland

Originally posted by: EagleKeeper

Originally posted by: leeland
do the fields that are in the movie table have to be primary keys ???


so if I want to match up the format field(which would be a 1 or 0) in the movie table and the format index(which would be the PK) in the format table...does that link work ? Format(num) ----format index(Pk) Or will I get an error ?


Thanks for the help guys

Leeland

The index field in the sub-tables should be primary keys.
The corresponding fields in the main table should just be tagged as indexed.

As to the actual programming of the query, you will be on your own.
I have not played around with Access forms since 2000

When you tag a field in a main table as indexed, what does that do ? and why do you need to do that ?

thanks,

Leeland

It means access creates a little lookup table so that when you query on that field, it can quickly look for fields that match and sort by that field. This "little lookup table" is known as an index. e.g. an index on title would be highly recommended so that when he searches on title it doesn't have to look at the movie table row by row and consider every single row, it can just look at the index which yield much quicker results.