Solved

MS Access Relationship

Posted on 2007-04-09
4
199 Views
Last Modified: 2010-03-18
Below are my tables ,, Can anyone make sense of the relationships because I'm getting confused of my own tables and don't know if i have it right. Thoughts anyone if I have the Primary and Foreign Sets correctly? Suggestions, Tips?

Cyclist
PK Cyclist_ID
Name
Phone Num
StreeAdd
State
City
Zip

Bicyle
PK Bicycle ID
BrandName
Make
Model
Size

Events
PK Events_ID
Event Type
Location
City
State
Zip
FK1 Cyclist_ID

Apparel
PK Apparel_ID
BrandName
Size
Style

Equipment
PK Equipment_ID
Wheels
Frame
Handlebars
Reflectors
FK1 Bicyle_ID
0
Comment
Question by:shoris
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 75
ID: 18876820
Why is the question not in the Access Database Zone ?  Seems it should be.

mx
0
 

Author Comment

by:shoris
ID: 18876830
Sorry. wrong zone.
0
 
LVL 35

Expert Comment

by:YZlat
ID: 18876925
Is there any relation between bicycle and cyclist? Is the particular cyclist only uses certain bicycles or any of them?

Also do you need to keep track which bicycle was used for a particular event? If so, you'll need to add BicycleID as a foreign key to the Event table.

Otherwise all the relationships seem fine.

What is apparel table for?
0
 
LVL 13

Accepted Solution

by:
adraughn earned 500 total points
ID: 18876945
before you get into the relationships, you may want to rethink your table designs.

1. Eliminate spaces in field names. you will regret every one of them.
2. Try not to use your IDs as primary keys unless necessary. (assuming your IDs are autonumbers) Use your data as primary keys and then use your id's as foreign keys to other tables. Using your data as PKswill prevent duplication.
3. You need more tables to normalize your data. For instance, look at your Bicycle table:

Bicyle
PK Bicycle ID
BrandName
Make
Model
Size

There can be more than one model per make and more than one size per model. so your data will look like this

MyBrand   MyMake   MyModel1   MySize1
MyBrand   MyMake   MyModel1   MySize2
MyBrand   MyMake   MyModel2   MySize1
MyBrand   MyMake   MyModel2   MySize2
MyBrand   MyMake   MyModel2   MySize3

this is duplicating data. Common rule of thumb is that if it is not a primary key, you may want to consider moving it into another table.

You might also want to look at your table design for cyclist. Under this design, each cyclist can have only one address and only one phone and it does not specify phone type.

As for your relationahips as your tables are currently, are those "ID" fields autonumber fields or true data?

adria
0

Featured Post

 Database Backup and Recovery Best Practices

Join Percona’s, Architect, Manjot Singh as he presents Database Backup and Recovery Best Practices (with a Focus on MySQL) on Thursday, July 27, 2017 at 11:00 am PDT / 2:00 pm EDT (UTC-7). In the case of a failure, do you know how long it will take to restore your database?

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

615 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question