defining multiple tables for a newbie

[tracer]

Hi. I have a school project where I need to design a database with four
tables. Will there be 4 different primary keys and 4 different foreign keys?
Does this mean that each table has to relate to the previous table or does
the first one relate to all the others?

Any advice would be appreciated. Thanks.

tracer

 

[Westaradg]

Look at the samples that came with Access, then look at the tables for a
general guideline then you can click the relationship icon on the toolbar and
see how each table relates to each of the others.

Good luck, one new guy to another.

 

[mnature]

Let's say you have four tables:

tbl_TableOne
TableOneID (PK)

tbl_TableTwo
TableTwoID (PK)

tbl_TableThree
TableThreeID (PK)

tbl_TableFour
TableFourID (PK)

None of these tables can relate to each other, but you have four tables.

You can make one of them relate to the other three:

tbl_TableOne
TableOneID (PK)
TableTwoID
TableThreeID
TableFourID

tbl_TableTwo
TableTwoID (PK)

tbl_TableThree
TableThreeID (PK)

tbl_TableFour
TableFourID (PK)

You could use this design if you wanted a primary table (tbl_TableOne) that
references data on the other three tables (tbl_TableTwo, tbl_TableThree and
tbl_TableFour).

You could also do this:

tbl_TableOne
TableOneID (PK)

tbl_TableTwo
TableTwoID (PK)
TableOneID
TableThreeID

tbl_TableThree
TableThreeID (PK)
TableFourID

tbl_TableFour
TableFourID (PK)

In this design, you use tbl_TableTwo as a way of combining various data from
tbl_TableOne and tbl_TableThree, with tbl_TableFour contributing data towards
tbl_TableThree.

You will usually have a primary key for each table. Foreign keys are used
to build relationships between various tables. Your database design
determines how you use foreign keys.

Are you thoroughly confused, now?

 

[tracer]

Look at the samples that came with Access, then look at the tables for a
general guideline then you can click the relationship icon on the toolbar and
see how each table relates to each of the others.

Good luck, one new guy to another.

Fellow Newbie,

Thanks. Looking at the sample database helped a lot. At least I have a vision
of what I'm doing.

tracer

 

[tracer]

Let's say you have four tables:

tbl_TableOne
TableOneID (PK)

tbl_TableTwo
TableTwoID (PK)

tbl_TableThree
TableThreeID (PK)

tbl_TableFour
TableFourID (PK)

None of these tables can relate to each other, but you have four tables.

You can make one of them relate to the other three:

tbl_TableOne
TableOneID (PK)
TableTwoID
TableThreeID
TableFourID

tbl_TableTwo
TableTwoID (PK)

tbl_TableThree
TableThreeID (PK)

tbl_TableFour
TableFourID (PK)

You could use this design if you wanted a primary table (tbl_TableOne) that
references data on the other three tables (tbl_TableTwo, tbl_TableThree and
tbl_TableFour).

You could also do this:

tbl_TableOne
TableOneID (PK)

tbl_TableTwo
TableTwoID (PK)
TableOneID
TableThreeID

tbl_TableThree
TableThreeID (PK)
TableFourID

tbl_TableFour
TableFourID (PK)

In this design, you use tbl_TableTwo as a way of combining various data from
tbl_TableOne and tbl_TableThree, with tbl_TableFour contributing data towards
tbl_TableThree.

You will usually have a primary key for each table. Foreign keys are used
to build relationships between various tables. Your database design
determines how you use foreign keys.

Are you thoroughly confused, now?

Hi. I have a school project where I need to design a database with four
tables. Will there be 4 different primary keys and 4 different foreign keys?

[quoted text clipped - 4 lines]

tracer

mnature,

Thanks for the response. If I have 5 primary keys and 5 foreign keys, tell me
if they should be linked like this:

Table 1 PK relates to the FK of table 2.
Table 2 PK, relates to the FK of table 3.
Table 3 PK, relates to the FK of table 4.
Table 4 PK, relates to the FK of table 5.
Table 5 PK, relates to ?

I'm having a little trouble figuring out how the tables will come together
according to my topic. My topic has to do with chicken breeds. My tables so
far are: Table 1 - Chickens, Table 2 - Breeds, Table 3 - Equipment, Table 4 -
Educational Supplies, and Table 5 - Health Supplies. Are these reasonable
tables for taking an inventory of chicken breeds? Will queries be able to
bring these topics together to answer questions?

Thanks for any input.

tracer

 

[John Vinson]

Thanks for the response. If I have 5 primary keys and 5 foreign keys, tell me
if they should be linked like this:

Table 1 PK relates to the FK of table 2.
Table 2 PK, relates to the FK of table 3.
Table 3 PK, relates to the FK of table 4.
Table 4 PK, relates to the FK of table 5.
Table 5 PK, relates to ?

You're misunderstanding how relationships work.

There isn't a formulaic structure with Table 1 and Table 2 and Table
3. There is NO constraint in Access requiring that one table must
relate to some other specific table.

Instead, your tables should be set up in a "Data Model" in which the
relationships between tables reflect relationships in the real world.
Each table represents a group of Entities (real-life persons, things,
or events), and you need to look at the real world and figure out how
those entities are logically related to one another.

I'm having a little trouble figuring out how the tables will come together
according to my topic. My topic has to do with chicken breeds. My tables so
far are: Table 1 - Chickens, Table 2 - Breeds, Table 3 - Equipment, Table 4 -
Educational Supplies, and Table 5 - Health Supplies. Are these reasonable
tables for taking an inventory of chicken breeds? Will queries be able to
bring these topics together to answer questions?

The only obvious relationship here is between Chickens and Breeds -
each chicken has one and only one breed (assuming that you aren't
hybridizing them), but each Breed value can apply to zero, one, or
many chickens. This would imply that the Breed table has a BreedID (or
you could just use the text breed name as the Primary Key since it's
unique and not likely to change); there would be a matching field in
the Chickens table, either BreedID or the text breed itself, as a
foreign key.

I have NO idea how the other three tables relate to each other, to
chickens, or to breeds. Is a given piece of equipment, or a given item
of Health Supplies, used for a single chicken? for many chickens? Only
for Plymouth Rock Red chickens? or do they have nothing to do with
chickens? WE can't tell you how they are related, because we don't
know your business; hopefully you do, and you can decide how a given
piece of equipment relates to the Entities modeled by your other
tables!

John W. Vinson[MVP]

 

[mnature]

As John has mentioned, there is no boilerplate for how tables relate to each
other. If there was, then companies probably wouldn't need database
programmers/administrators. Every database starts by looking at what your
information is. Since you didn't tell us before what your information was,
there was absolutely no way we could advise you about how the tables would
relate.

So, you have data about Chickens, Breeds, Equipment, Educational Supplies,
and Health Supplies. This is a good start. However, just putting
information into tables doesn't make it a database. There has to be some
reason for putting all this information into the database together. So, let
me make a wild guess: You are supposed to be keeping track of how many
chickens you have, and what breeds they are.

tbl_Chickens
ChickenID (PK)
ChickenBreedID (FK)
DateAcquired

tbl_Breeds
ChickenBreedID (PK)
BreedName

You seem to also need some other tables, but there isn't enough information
for setting those up.

 

[tracer]

Thanks for the response. If I have 5 primary keys and 5 foreign keys, tell me
if they should be linked like this:

[quoted text clipped - 4 lines]

Table 4 PK, relates to the FK of table 5.
Table 5 PK, relates to ?

You're misunderstanding how relationships work.

There isn't a formulaic structure with Table 1 and Table 2 and Table
3. There is NO constraint in Access requiring that one table must
relate to some other specific table.

Instead, your tables should be set up in a "Data Model" in which the
relationships between tables reflect relationships in the real world.
Each table represents a group of Entities (real-life persons, things,
or events), and you need to look at the real world and figure out how
those entities are logically related to one another.

I'm having a little trouble figuring out how the tables will come together
according to my topic. My topic has to do with chicken breeds. My tables so
far are: Table 1 - Chickens, Table 2 - Breeds, Table 3 - Equipment, Table 4 -
Educational Supplies, and Table 5 - Health Supplies. Are these reasonable
tables for taking an inventory of chicken breeds? Will queries be able to
bring these topics together to answer questions?

The only obvious relationship here is between Chickens and Breeds -
each chicken has one and only one breed (assuming that you aren't
hybridizing them), but each Breed value can apply to zero, one, or
many chickens. This would imply that the Breed table has a BreedID (or
you could just use the text breed name as the Primary Key since it's
unique and not likely to change); there would be a matching field in
the Chickens table, either BreedID or the text breed itself, as a
foreign key.

I have NO idea how the other three tables relate to each other, to
chickens, or to breeds. Is a given piece of equipment, or a given item
of Health Supplies, used for a single chicken? for many chickens? Only
for Plymouth Rock Red chickens? or do they have nothing to do with
chickens? WE can't tell you how they are related, because we don't
know your business; hopefully you do, and you can decide how a given
piece of equipment relates to the Entities modeled by your other
tables!

John W. Vinson[MVP]

Hi John,

I think the trouble I'm having is that this is not for a business, but for a
school project. The project consists of making an inventory of something
using at least 4 tables. The example in class was an inventory of a baseball
card collection and an inventory of a person's artwork. How do you come up
with tables for a hobby without having tables like supplier, or invoice
number, etc.?

Do you have any ideas on what tables I could use besides Chickens and Breeds
to use for my project?

Thanks for any help.

tracer

 

[mnature]

You could try making an inventory database for your music or movie
collection. Start with a table for information specific to music or movies
(such as title, publication date), and then think of some aspects that could
go into separate tables, and be linked as foreign keys back to the main table
(for movies could use actor, producer, genre, director, rating).

 

[tracer]

You could try making an inventory database for your music or movie
collection. Start with a table for information specific to music or movies
(such as title, publication date), and then think of some aspects that could
go into separate tables, and be linked as foreign keys back to the main table
(for movies could use actor, producer, genre, director, rating).

I think the trouble I'm having is that this is not for a business, but for a
school project. The project consists of making an inventory of something

[quoted text clipped - 9 lines]

tracer

Thanks so much mnature. Those are great ideas. I like the movie idea and I
have well over one hundred (which happens to be the number of records we must
have).

Table 1 could be movie name with fields like movie ID, movie name, ISBN
number, Date of Release.
Table 2 could be type of movie with fields like comedy, romance, drama,
horror, action/adventure.
Table 3 could be movie rating with fields like G, PG, PG-13, etc.
Table 4 could be movie format with fields DVD or VHS
Table 5 could be director, like you suggested
Table 6 could be actor, like you suggested.

Thanks again. I've got a little research to do now.

tracer

Thanks again.

tracer

 

[mnature]

A challenge for you: What if you want to associate more than one actor with
a particular movie? That probably goes beyond the scope of your school
assignment, but illustrates a very powerful aspect of relational databases.

You could try making an inventory database for your music or movie
collection. Start with a table for information specific to music or movies
(such as title, publication date), and then think of some aspects that could
go into separate tables, and be linked as foreign keys back to the main table
(for movies could use actor, producer, genre, director, rating).

I think the trouble I'm having is that this is not for a business, but for a
school project. The project consists of making an inventory of something

[quoted text clipped - 9 lines]

tracer

Thanks so much mnature. Those are great ideas. I like the movie idea and I
have well over one hundred (which happens to be the number of records we must
have).

Table 1 could be movie name with fields like movie ID, movie name, ISBN
number, Date of Release.
Table 2 could be type of movie with fields like comedy, romance, drama,
horror, action/adventure.
Table 3 could be movie rating with fields like G, PG, PG-13, etc.
Table 4 could be movie format with fields DVD or VHS
Table 5 could be director, like you suggested
Table 6 could be actor, like you suggested.

Thanks again. I've got a little research to do now.

tracer

Thanks again.

tracer

 

[tracer via AccessMonster.com]

A challenge for you: What if you want to associate more than one actor with
a particular movie? That probably goes beyond the scope of your school
assignment, but illustrates a very powerful aspect of relational databases.

Hi mnature,

I was just speaking with my instructor yesterday after class and he realized
that it will be "a challange", as well since I'd have more than just one
actor per movie. To tell you the truth, I'm more confused now then I was
before I spoke to him.

I want to make a simple 4-5 table database, with only a one to many
relationship. I would like my tables to include: Movie table, Actor table,
Director table, Genre table, and Rating table.

Can I make the movie table the one and the others the many? It's not
registering to me how these relationships should be set up for a workable
database.I have researched how to set up these tables in this way, and I'm
just not getting it. Maybe it will "hit me" with more practice, but so far....
nothing.

This website has been great. Truthfully, I'm learning more from other users
questions than I am learning from my text and instructor. The online
Microsoft Access is very helpful and I get it when there are only two tables.
I can't seem to find anything when there are more than two tables involved.

Thanks in advance for any help you can send my way.

tracer

[quoted text clipped - 28 lines]

tracer

 

[mnature]

This is a common problem faced by databased programmers. You have some data
(such as movies) that you want to relate to some other data (such as actors).
However, you don't want to have duplicate movie names, or duplicate actor
names. You need to use a third table that will collect all of the relations
that occur. For instance:

tbl_Movies
MovieID (PK)
MovieName

tbl_Actors
ActorID (PK)
ActorName

tbl_MovieActors
MovieActorID (PK)
MovieID
ActorID

Both tbl_Movies and tbl_Actors will be in a one-to-many relationship with
tbl_MovieActors. Any particular movie can have any number of actors
associated with it, and any particular actor can have any number of movies
associated with them. So each of your Bruce Lee movies can be associated
with Bruce Lee. And, on the other hand, a number of other actors can also be
associated with any of your Bruce Lee movies.

You will set up your relationship by relating the common fields. Then you
will set up a query, using the query wizard, using tbl_MovieActors (all
fields), then tbl_Movies (MovieName), and tbl_Actors (ActorName). Then set
up a form, using form wizard, based on that query. At one point, it will ask
how you want to view your data. At this point, choose "by Movies" and then
continue to end of wizard. You should end up with a form and subform. The
main form is where you put in, or choose, the movie. The subform is where
you put in, or choose, the actor. There will be two sets of arrows, one for
the main window, one for the sub-window, that you can use to navigate through
the data.

If you can figure this one out, you will be doing very well.

 

[tracer via AccessMonster.com]

Hi mnature,

Thanks so much for going the extra mile with me on this. I really appreciate
it. From your advice and a little more research, it's coming together for me.
I see the light at the end of the tunnel........!

Thanks again.

tracer