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
shorisAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Windows Networking

From novice to tech pro — start learning today.