Relationship and tables questions...

Hi I created a databse system but i have a problem figuring out how to
create some tables / relationships....

The part of the systems that i am having problems with is the
following i have a person (person table) that a specific date & time
might be busy / available / tentative.

I tried to create a different table for date and time so that i can
define the times 01-24 and then i can reuse them.

But i am having trouble thinging the relationship / tables and how
this can happend ..

one person can be busy / available / tentative at many dates but busy
availble tentative at only one time...

I am confused ;-(

I am also confused about the contents of the tables.i think that date
must have date(primary key) and person foreign key :e.g 12/12/2009 .
time should have time (primary key) and date foreign key..
Please help me.. ;-)

Re: Relationship and tables questions...

I would suggest an Assignment table to keep track of "busy", with entries
only for "busy" and "tenative" by date and time, with a foreign key to the
person's record in the Person Table.

If you need that person for an "assignment", you will likely know the
date/time, and if you query the table (or join to it) on that date and time,
and find no record, you'll know the person is available.

On the other hand, you can join that table with a table of date/time, and
select "Is Null" for the "Busy / Tenatively Busy" field to get a display of
the person's availability.

I am confident that, in the long term, that will be much easier to deal with
than trying to create a table with every hour of every day for a reasonable
past/present/future time frame for each person. Such a table would be large
and cumbersome.

Larry Linson
Microsoft Access MVP


"Ivan" wrote in message
news:71259273-3c51-488e-a9bb-4dbeb95877bd@y5g2000hsf.googleg roups.com...
> Hi I created a databse system but i have a problem figuring out how to
> create some tables / relationships....
>
> The part of the systems that i am having problems with is the
> following i have a person (person table) that a specific date & time
> might be busy / available / tentative.
>
> I tried to create a different table for date and time so that i can
> define the times 01-24 and then i can reuse them.
>
> But i am having trouble thinging the relationship / tables and how
> this can happend ..
>
> one person can be busy / available / tentative at many dates but busy
> availble tentative at only one time...
>
> I am confused ;-(
>
> I am also confused about the contents of the tables.i think that date
> must have date(primary key) and person foreign key :e.g 12/12/2009 .
> time should have time (primary key) and date foreign key..
> Please help me.. ;-)

Re: Relationship and tables questions...

I'd default that a person is available unless otherwise stated
I'd set my tables as follows:

tblPerson
person_id Autonumber
person_name Text

tblAppointments
appointment_id Autonumber
appointment_type Number (byte, 1=Tentative;2=Busy)
appointment_start Date/Time
appointment_end Date/Time
appointment_person_id Number (FK to tblPerson.person_id)

I hope that'll get you started.


Dominic

"Ivan" wrote in message
news:71259273-3c51-488e-a9bb-4dbeb95877bd@y5g2000hsf.googleg roups.com...
> Hi I created a databse system but i have a problem figuring out how to
> create some tables / relationships....
>
> The part of the systems that i am having problems with is the
> following i have a person (person table) that a specific date & time
> might be busy / available / tentative.
>
> I tried to create a different table for date and time so that i can
> define the times 01-24 and then i can reuse them.
>
> But i am having trouble thinging the relationship / tables and how
> this can happend ..
>
> one person can be busy / available / tentative at many dates but busy
> availble tentative at only one time...
>
> I am confused ;-(
>
> I am also confused about the contents of the tables.i think that date
> must have date(primary key) and person foreign key :e.g 12/12/2009 .
> time should have time (primary key) and date foreign key..
> Please help me.. ;-)