Solved

MS Access Relationship

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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Many of us in IT utilize a combination of roaming profiles and folder redirection to ensure user information carries over from one workstation to another; in my environment, it was to enable virtualization without needing a separate desktop for each…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

708 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

18 Experts available now in Live!

Get 1:1 Help Now