DB Design Was RE: [ale] [OT] Left Join SQL help...

[Zyman, Andy]

Cade and Robert,

this is what i think might be working:

person_table ( person_id, name )

main_table ( person_id, date, food, exercise, weight ... )

all descriptions goes to appropriate fields. If you need to add another
activity - it's not a problem.
plus, in case of full scan - it's better to have one table ( if full scan
can be good at all %) ) than to jump around couple of them.

AZ

P.S> Put index on id and date and you should be ok.

P.P.S> also by eliminating FK you are reducing risks of locks. I don't think
it's a big deal for this type of application though...

I think you are reading about normal forms and staff.  Yes, this is good to
have this knowledge in head, BUT, from my experience, there are 2 major
objectives :
1. keep it simple
2. performance is THE Key

Objects ( seems like you were trying to match/create them ) are very nice
_concept_ , but I'm afraid they are not for SQL world..
AZ



> -----Original Message-----
> From: Robert Coggins [mailto:ale at cogginsnet.com]
> Sent: Wednesday, April 02, 2003 9:56 AM
> To: ale at ale.org
> Subject: Re: [ale] [OT] Left Join SQL help...
> 
> 
> I am not sure of the use of the ERD you have here.  This 
> question is purely
> for my own curiosity and education.  But here ti goes.  Would 
> it be so wrong
> to do this...
> 
> Instead of having four tables for exercise, food, weight, and 
> liquids would
> it not be easier to just have one table called maybe activities with
> person_id, date, exercise_notes, AND say maybe an ACTIVITY 
> attribute that
> could be a value of either exercise, food, weight, and 
> liquids? Well, tell
> me what you all think.  I am trying to learn...
> 
> -Rob
> 
> 
> ----- Original Message -----
> From: "Cade Thacker" <linux at cade.org>
> To: <ale at ale.org>
> Sent: Tuesday, April 01, 2003 11:28 PM
> Subject: [ale] [OT] Left Join SQL help...
> 
> 
> > OK, i have been googling and testing for 3 hours and I can 
> not seem to
> > find the correct answer to this question. This is so 
> freakin simple that I
> > embarassed to ask, but here goes. Very easy. 5 tables...
> >
> > person
> > ====
> > person_id
> > name
> > ====
> >
> > 4 identical tables, exercise, food, weight, liquids.
> >
> > exercise
> > ====
> > person_id // matches person.person_id
> > date // the date ;)
> > exercise_notes // text of what exercise you did that day
> > ==
> >
> >
> > The information I want is based on a person id and a date. I want to
> > return the exercise, food, liquids, and weight entry for 
> that person, on
> > that date.
> >
> > So given person_id 1 and date 2003-03-31, say they have 
> only a exercise
> > entry, what I want back is:
> >
> > person = cade
> > exercise = "Walked 1 mile"
> > food = NULL
> > weight = NULL
> > liquids = NULL
> >
> > I know that I can do this with an OUTER (LEFT) join, but the correct
> > syntax is eluding me. I have tried
> >
> > SELECT name, exercise_notes, food_notes, weight_notes, liquids_notes
> > FROM person
> > LEFT JOIN weight ON person.person_id = weight.person_id
> > LEFT JOIN liquids ON person.person_id = liquids.person_id
> > LEFT JOIN exercise ON person.person_id = exercise.person_id
> > LEFT JOIN food ON person.person_id = food.person_id
> >
> > WHERE person.person_id = 1
> > AND (weight.date ='2003-03-30' OR weight.date = NULL)
> > AND (liquids.date ='2003-03-30' OR liquids.date = NULL)
> > AND (exercise.date ='2003-03-30' OR exercise.date = NULL)
> > AND (food.date ='2003-03-30' OR food.date = NULL)
> >
> > #end of sql
> >
> > Did I give enough info?  All of the help on google just 
> returns how to
> > outer join 2 tables.
> >
> > TIA....
> >
> > --cade
> >
> > On Linux vs Windows
> > ==================
> > Remember, amateurs built the Ark, Professionals built the Titanic!
> > ==================
> >
> >
> >
> > _______________________________________________
> > Ale mailing list
> > Ale at ale.org
> > http://www.ale.org/mailman/listinfo/ale
> >
> 
> _______________________________________________
> Ale mailing list
> Ale at ale.org
> http://www.ale.org/mailman/listinfo/ale
> 
_______________________________________________
Ale mailing list
Ale at ale.org
http://www.ale.org/mailman/listinfo/ale