How to begin designing database

[Carol]

Hi -

Please keep in mind that I'm an amateur!

I'm am designing a database that allows users to match
their interests with places that they can travel to. For
example, people interested in art should visit Paris or
New York.

I have a table with the traveler's information such as
name, etc...just basic demographics. I have another
table with a list of interests. The travelers table
contains a primary key of travelerid. The interest table
contains a primary key of interestid. This table also
contains a field, travelerid. Ive linked the two tables
together by the these two fields.

Now, I'm lost as to where to go. I need to have the user
enter their demographic information, then enter choose
their interests from the interests table and have those
interest choices match up and return results on where
they should travel to. Again, if someone chooses art as
one interest and painting as another, I need those two
interests to be "keywords" and pull results from a cities
table that would show Paris, New York, San Francisco,
etc...

How can I design this? How many more tables do I need to
create and how can I link the tables together? Which
fields do I need and which fields do I link to each
other?

What about forms? Do I need to create certain forms for
this? If so, how should I create the forms?

Or, is there a way to create a query that will return the
desired results and have the results displayed on a
form? Is that possible?

Please help! Thanks!

 

[John Vinson]

Hi -

Please keep in mind that I'm an amateur!

I'm am designing a database that allows users to match
their interests with places that they can travel to. For
example, people interested in art should visit Paris or
New York.

I have a table with the traveler's information such as
name, etc...just basic demographics. I have another
table with a list of interests. The travelers table
contains a primary key of travelerid. The interest table
contains a primary key of interestid. This table also
contains a field, travelerid. Ive linked the two tables
together by the these two fields.

That's your (very understandable!) mistake. With this design you have
only one value for travelerid in the interest table. Since a field can
have only one value, this would mean that one and only one of your
travelers is allowed to be interested in art! See below.

Now, I'm lost as to where to go. I need to have the user
enter their demographic information, then enter choose
their interests from the interests table and have those
interest choices match up and return results on where
they should travel to. Again, if someone chooses art as
one interest and painting as another, I need those two
interests to be "keywords" and pull results from a cities
table that would show Paris, New York, San Francisco,
etc...

How can I design this? How many more tables do I need to
create and how can I link the tables together? Which
fields do I need and which fields do I link to each
other?

You need at least two more tables. Whenever you have a Many to Many
relationship - one person can have many interests, and many people can
share the same interest - you need *a new table* to model this
relationship. The TravelerInterest table would have fields TravelerID
and InterestID, each serving as a linking field ("foreign key" it's
called) to the respective table; the two fields together would
constitute the Primary Key of TravelerInterest.

There'd be a similar table for CityAttractions; for instance New York
would have records for Theater, Art, Architecture, Slums, etc.

What about forms? Do I need to create certain forms for
this? If so, how should I create the forms?

You'ld probably have a fair number of Forms with Subforms; for
instance you could have a Traveler form with fields for the person's
demographic data, with a Subform based on TravelerInterests. On this
Subform - use a Continuous Subform I'd say - you'ld have a combo box
based on the Interests table, displaying the interest name ("Art") but
storing the ID into the TravelerInterests table.

Or, is there a way to create a query that will return the
desired results and have the results displayed on a
form? Is that possible?

Yes; the query would not be updateable, but you could create a Query
joining Traveler to TravelerInterests by TravelerID; join
TravelerInterests to CityAttractions by InterestID; join
CityAttractions to Cities by CityID; and finally join Interests to
TravelerInterests by InterestID. Pick up the demographic data from
Travelers, the name of the interest from Interests, and the city name
from Cities.


John W. Vinson[MVP]