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

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.

On Sep 29, 2:08 pm, "Paul Lautman" <paul.laut...@btinternet.com>
wrote:
> shadke...@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.- Hide quoted text -
>
> - Show quoted text -

Hello sir, I know some sql syntax, so I've started from the beginning
already with mysql/database design. Of course, I'm by no means even
at an average level of database knowledge. But I've read several
articles on using composite primary keys...mysql allows me to do
that. The data I'm tracking seems like it needs composite primary
keys in order to provide uniqueness to each row of data. Also, most
of the tables/database examples that I've looked at seem to be using
more ID numbers than dated/timed data. So, I guess what I'm getting
at is trying to get advice on how I might arrange the other part of
the composite key (times) so I can relate the tables together. If
I'm way off, so be it.
Shad

shadkeene@hotmail.com wrote:
>
> Hello sir, I know some sql syntax, so I've started from the beginning
> already with mysql/database design. Of course, I'm by no means even
> at an average level of database knowledge. But I've read several
> articles on using composite primary keys...mysql allows me to do
> that. The data I'm tracking seems like it needs composite primary
> keys in order to provide uniqueness to each row of data. Also, most
> of the tables/database examples that I've looked at seem to be using
> more ID numbers than dated/timed data. So, I guess what I'm getting
> at is trying to get advice on how I might arrange the other part of
> the composite key (times) so I can relate the tables together. If
> I'm way off, so be it.
> Shad

Tables can certainly have composite primary keys. But they can not have two
primary keys ,which is what you were suggesting.

On Sun, 30 Sep 2007 21:25:20 +0100, "Paul Lautman"
<paul.lautman@btinternet.com> wrote:

>shadkeene@hotmail.com wrote:
>>
>> Hello sir, I know some sql syntax, so I've started from the beginning
>> already with mysql/database design. Of course, I'm by no means even
>> at an average level of database knowledge. But I've read several
>> articles on using composite primary keys...mysql allows me to do
>> that. The data I'm tracking seems like it needs composite primary
>> keys in order to provide uniqueness to each row of data. Also, most
>> of the tables/database examples that I've looked at seem to be using
>> more ID numbers than dated/timed data. So, I guess what I'm getting
>> at is trying to get advice on how I might arrange the other part of
>> the composite key (times) so I can relate the tables together. If
>> I'm way off, so be it.
>> Shad
>
>Tables can certainly have composite primary keys. But they can not have two
>primary keys ,which is what you were suggesting.

Just an addition: Apart from a (composite) primary key, any
table can have other keys by adding UNIQUE indexes.
These can be referred to by foreign keys in any related tables.

It's also possible to define a primary key on one column and a
UNIQUE constraint (index) on one or more other columns.

my 2 cents
--
( Kees
)
c[_] Like a lot of husbands throughout history, Mr. Webster would sit
down and try to talk to his wife. As soon as he'd say something
though, she'd fire back with, "And just what the hell is THAT
supposed to mean?" Thus, Webster's Dictionary was born. (#6)

> >> Hello sir, I know some sql syntax, so I've started from the beginning
> >> already with mysql/database design. Of course, I'm by no means even
> >> at an average level of database knowledge. But I've read several
> >> articles on using composite primary keys...mysql allows me to do
> >> that. The data I'm tracking seems like it needs composite primary
> >> keys in order to provide uniqueness to each row of data. Also, most
> >> of the tables/database examples that I've looked at seem to be using
> >> more ID numbers than dated/timed data. So, I guess what I'm getting
> >> at is trying to get advice on how I might arrange the other part of
> >> the composite key (times) so I can relate the tables together. If
> >> I'm way off, so be it.
> >> Shad
> >
> >Tables can certainly have composite primary keys. But they can not have
two
> >primary keys ,which is what you were suggesting.
>
> Just an addition: Apart from a (composite) primary key, any
> table can have other keys by adding UNIQUE indexes.

Unique Constraints would actually be what you're referring to. Although
they are mostly implemented by using some kind of index, an "index" has
no place in the SQL standard. And to keep things in par with other
database system, especially with regard to this statement:

> These can be referred to by foreign keys in any related tables.

You shouldn't talk about unique indices in this context.

> It's also possible to define a primary key on one column and a
> UNIQUE constraint (index) on one or more other columns.


--
Martijn Tonies
Database Workbench - development tool for MySQL, and more!
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com