shadkeene@hotmail.com wrote:
> Hi,
> I've begun to design a database in a somewhat organized manner. I'd
> like to know, before I go any further, if I'm on the right track.
>
> So, basically I'm tracking weather data from a user (input twice a
> day) and then inputting automated data at varying times during the
> day. I'd like to relate all the tables together in an efficient way,
> but I don't exactly know how this will happen.
>
> I'll give you some example tables:
> User input...all types of subjective data twice daily...so I'll have
> two rows per day.
> Observed Wind Data...various aspects of wind in 1-hr increments.
> Model Wind Data...various aspects of wind data compiled and presented
> in 1 row once per day.
> Here are the tables themselves:
>
> Table 1:User Input
>
> DATE TIME(z) FRONT WNDSHFTSFO CLDCLEAR
> 2007-08-21 12 APPRCHFRNT 16Z 18Z
> 2007-08-21 21 DEPARTFRNT NULL SHFTCLR
> 2007-08-22 12 NULL NULL SHFTCLR
> 2007-08-22 21 NULL NULL SHFTCLR
> 2007-08-23 12 APPRCHFRNT 17Z 21Z
> 2007-08-23 21 APPRCHFRNT 21Z 23Z
>
> Table 2:Observed wind shift
> DATE TIME(z) OBWNDSHFTSFO
> 2007-08-21 12 NULL
> 2007-08-21 13 NULL
> 2007-08-21 14 14
> 2007-08-21 15 NULL
> 2007-08-21 16 NULL
> 2007-08-21 17 NULL
> 2007-08-22 12 NULL
> 2007-08-22 13 NULL
> 2007-08-22 14 NULL
> 2007-08-22 15 NULL
> 2007-08-22 16 NULL
> 2007-08-22 17 NULL
> 2007-08-23 12 NULL
> 2007-08-23 13 NULL
> 2007-08-23 14 NULL
> 2007-08-23 15 NULL
> 2007-08-23 16 NULL
> 2007-08-23 17 17
>
>
> Table 3: Model Wind data
> DATE TIME(z) MODWNDSHFTSFO
> 2007-08-21 12 14
> 2007-08-22 12 NULL
> 2007-08-23 12 18
>
> So, I'd like to relate tables like these that have different times
> (which I presume will be the second primary key in the tables). For
> instance, how would I take all days that had fronts (indicated by
> anything other than NULL in the FRONT column) and reference those
> dates automatically (08-21-2007 and 08-23-2007) to retrieve data from
> the other tables just on those dates? Perhaps this is confusing...I've
> done some reading on normalization and have tried to implement that,
> but I'm not comprehending how I'll be able to get useful views/
> statistics from the joining of the tables.
>
> Or could I just be specific in my query and say, "on days when a front
> occurred, show me the following: WNDSHFTSFO, OBWNDSHFTSFO when not
> NULL, and MODWNDSHFTSFO"...could I do that?
> I have a lot more data that I'd like to be inter-relating between
> tables, but I'm trying not to create a bunch of tables and then
> realize I can't effectively compare them. I can clarify if any of the
> above is not clear. Thanks!!
> Shad

Well I think you need to start with the basics of database tables and SQL!

The very first thing you said, in the Subject, is already totally wrong. A
table can have only 1 primay key and it must be unique. So you cannot have
the date on its own as a primary key if there can be more than one record
with the same date in a table.

You could most likely write a query that gives the effect of "on days when a
front
occurred, show me the following: WNDSHFTSFO, OBWNDSHFTSFO when not
NULL, and MODWNDSHFTSFO", but it would have to be written in SQL, and not in
the way that you have written it here.

There are important decisions to make on whether fields should allow NULL
values and what foreign keys to have in order to maintain a sensible
relationship. You really need to start from the beginning. People get paid a
lot of money to be able to design database schemas. If it was that easy,
they woul dnot be earning that sort of money.