two tables or one?

Question

two tables or one?

brian garson

SSC Rookie

Points: 28
More actions
September 22, 2006 at 12:24 pm

#169823

Pretty straight forward problem here, I'm just not that experienced with sql, so i was wondering if I could get some help:
each user has a daily journal that they can enter on the site, for each day the user will have to fill out the same field about food they've eaten 10-15 times, so my thoughts for the table were as such:
journal_entry_tb
pk journal_id
fk user_id
total_cals_eaten
date
journal_food_entry
pk id
fk journal_id
fk food_id
quantity
so for each day each user will have 1 journal_entry_tb and multiple (10-15) journal_food_entry's
the problems i foresee, are keeeping table 1 up to date with table 2.. should this all just be combined as one table, or are my assumptions correct?
(also if this post makes no sense, i could clarify more)

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Sreejith Sreedharan SSCertifiable Points: 5403 More actions · Answer 1

what do u mean by table 1(journal_entry_tb) up to date with table 2(journal_food_entry). I don't see any column thats repeating on both the tables (except PK-FK keys). Are u trying to update total_cals_eaten from quantity in table 2(journal_food_entry). If so u don't need that column in table1(journal_entry_tb) cos that can change each time an entry is added or updated on table 2(journal_food_entry).

Please clarify.

Thanks

Sreejith

brian garson SSC Rookie Points: 28 More actions · Answer 2

yeah the total_cals_eaten on table 2 for each journal_id should be added up and put into the other table, i was thinking this would save look up time as this value is used very often on the site..

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 3

Not sure you'd have to worry about the locks to create a simple sum... and, everytime someone made a new entry in the food table, you'd have to do an UPDATE in the daily entry table... now that's a lock you might want to concern yourself with...

I'm thinking the single table with calculated sums might be the way to go but, then again, I'm on a diet

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)