Solved

MS Access Relationship

Posted on 2007-04-09
4
174 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
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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Are you one of those front-line IT Service Desk staff fielding calls, replying to emails, all-the-while working to resolve end-user technological nightmares? I am! That's why I have put together this brief overview of tools and techniques I use in o…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

864 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now