Database Relationships Getting Complicated

[jeffharding]

I am doing workout software and am finding that my tables and their relationships are getting out of control. I was wondering if someone could go over my relationships and give me some pointers and advice...

ANyone interested, please respond to this post and i will email my database or email me at jharding@exxcess.com.

PS-i had it done, but there was a problem with one table not allowing me to update other tables when i included it in the query. so i am trying to redo it. please help guys!

 

[SgtJarrow]

Feel free to send a zipped version me at my work address...glad to take a look and help you out.

****************************
Only two things are infinite, the universe and human stupidity,
and I'm not sure about the former. (Albert Einstein)

Robert L. Johnson III
MCSA, CNA, MCP, Network+, A+
w: rljohnso@stewart.com
h: wildmage@tampabay.rr.com

 

[JeremyNYC]

Jeff,

I'd suggest posting a listing of your tables here--that way lots of people can help you, and lots of people can read the responses and learn from them.

You could just do it like this:

TableName
Name of Primary Key field
Description of the type of data in the table.

Also, you might want to do some reading on the subject of data normalization. I recommend one particular article so often that I put a copy of it up on my site. It's called "Fundamentals of Relational Database Design", and it's by Paul Litwin. People often find it very helpful.

Jeremy

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Access Databases for Non-Profit Organizations

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[jeffharding]

OK here is a basic outline of my tables and relationships

USERNAME TABLE
PK-USername
contains
-Cycle ID for workout cycle
-Diet_cycle_ID for meal plan cycle
-user info

CYCLE TABLE (Outlines the number of weeks you will be doing the workout for)
PK-Cycle_ID
Contains
-weeks in cycle
-start date of cycle
-end date of cycle
-cycle name

DIET CYCLE TABLE (Outlines the number of weeks you will be planning your diet for)
PK-Diet_Cycle_ID
Contains
-weeks in diet cycle
-start date of diet cycle
-end date of diet cycle
-diet cycle name
-number of meals per day

CYCLE_PLAN_TABLE (Outlines each day in the cycle and the day of week associated with it)
PK-Cycle_Plan_ID
Contains
-Cycle_ID
-DayOfWeek
-PLan Date

DIET_CYCLE_PLAN_TABLE (outlines each day in the diet cycle and the day of week associated with it)
PK-Diet_Cycle_Plan_ID
Contains
-Diet_Cycle_ID
-DayOfWeek
-Plan Date

DAY_OF_WEEK_TABLE
PK-DayofWeek (monday-sunday)

DAILY_ORDER_TABLE (Outlines the daily routine for each day in the workout, the exercises done and number of sets)
PK-Daily_Order_ID
Contains
-Cycle_Plan_ID
-Exercise_Set_ID (contains exercise and set number...)
-ORder Number

DAILY_MEAL_PLAN_TABLE (Outlines the way each meal is structured for each day. so many meals in a day, what food with what meal...)
PK-Daily_Meal_Plan_ID
Contains
-Diet_Cycle_Plan_ID
-Meal_ID (contains food and meal number...)
-Comments

EXERCISE_SET_TABLE (Defines a certain set for a certain exercise)
PK-ExerciseSet_ID
Contains
-Exercise_ID-what exercise?
-Set_ID -what set number?
-Comments

Meal_Table
PK-Meal_ID
Contains
-Food_Id (food name, serving size...)
-Meal_Number_ID (meal number, meal time, description)
-Comments


All other tables are descriptive and work pretty good. I am just having problems getting from outlining cycles to each outlining each day. IT was mostly between the cycle tables and the daily order tables. I had them joined by the day of week table, but then i couldnt edit data from the set table and exercise table (1 level down from exercise set table) when trying to do a query based on the plan date in the cycle tables. SO, here it is, let me know what you think. Thanks again!