If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
How to begin designing database
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! |
#2
|
|||
|
|||
On Mon, 24 Jan 2005 15:05:26 -0800, "Carol"
wrote: 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] |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Database connectivity?How many users can connect at once | Emmanuel | General Discussion | 9 | January 5th, 2005 01:07 AM |
Archiving A Database | PC User | General Discussion | 2 | November 2nd, 2004 11:16 PM |
Database Window Gone | DaveB | General Discussion | 2 | July 29th, 2004 12:24 AM |
You do not have exclusive access... ERROR | Robin | General Discussion | 1 | July 6th, 2004 01:18 AM |
Mutliple Tables lookup? | Westley | Database Design | 4 | June 15th, 2004 01:07 AM |