Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 214
  • Last Modified:

MS Access Relationship

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
shoris
Asked:
shoris
1 Solution
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Why is the question not in the Access Database Zone ?  Seems it should be.

mx
0
 
shorisAuthor Commented:
Sorry. wrong zone.
0
 
YZlatCommented:
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
 
adraughnCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now