Designing calendar databases

Is there a best practice/sample database design for creating database applications for scheduling events?

I am looking at creating an online work schedule using the .net calendar control tied back to a SQL Server database (think Outlook Calendar). I am at a loss as to how to efficiently design the database. Will I need to create an entry for every day? Is it prudent to use a datetime value for a primary key (I would have to whack the time portion)? How does one handle the number of days in a month/year?

Anyhoo... I would greatly appreciate any ideas/resources on how to design a database of this type.

Thanks in advance.I can't see any reason to have an entry for every day. Why have an entry if no events are scheduled for a certain day? Just store the events in an event table with a date as one of the columns.|||I am also, have you had any luck?|||You probably have a solution to this, but I was tasked with creating a calendar to schedule meeting rooms as one of my first projects for my current employer.

My table calendar has the following design.

CalendarID, int, autoinc
calendarDateTime, datetime
Appointment, varchar, 50
Reservedby, varchar, 50
Duration, varchar, 50
Freetime, varchar, 50
RoomValues, varchar, 50
AppointmentTime, varchar, 50

Each appointment is it's own row. The asp.net pages check to see if a appointment exists at the time the user selects, if not, it checks the duration which is in 1/2 hour increments if there is an overlap it won't enter the appointment. Otherwise if the time is free it inserts the record. I then query all the appointments for the day and build a table to display the information in a graphical way, Green background for free time, and yellow for occupied. I only display from 8:00AM to 5:00PM on the webpage, but allow scheduling to be anytime.

I hope this helps. BTW, it has been working like a charm since the first week I started here back in March.

Greg

Design Question

I have been asked to build a database that tracks children and households. I have come up with the following sample and would like some input. The idea is that a child could have two parents and each parent could live a different locations (Divorced). So I need to link the mother and father records to the child or children.

My real question is how I could then query a list of client relations (household) that could then be used possibly for a directory. For example;

Jimmy Smith Student
Jane Smith Mother
Jack Smith Father

Thank you for any help.

tblClients (all parents and children are entered here)
Client_ID
etc...
tblRelations (there is a one-to-many relation on the tblClients) One client could have many related client records. The client_ID of related client records is entered into a related_ID field of the tbRelations table.
Rel_ID
Client_ID
Related_ID
Relationship (mother, father etc...)I'd set it up as the following tables...

tblClients
ClientID int identity
ClientName varchar (30)
tblRelationshipTypes
RelationshipTypeID int identity
RelationshipTypeName varchar(15)
TblRelationships
ChildID int (stores the ClientID of the child)
ParentID int (stores the ClientID of the parent)
RelationshipTypeID int

You could then create the following view

SELECT Child.ClientID as ChildID,
Child.ClientName AS ChildName,
Parents.ClientName AS ParentName,
tblRelationshipTypes.RelationshipTypeName
FROM tblClients Child,
tblClients Parents,
tblRelationships,
tblRelationshipTypes
WHERE Child.ClientID = tblRelationships.ChildID AND
Parents.ClientID = tblRelationships.ParentID AND
tblRelationshipTypes.RelationshipTypeID = tblRelationships.RelationshipTypeID

which would return

ChildID ChildName ParentName RelationshipTypeName
---- ---------- ---------- -------
1 Joey Mrs. Smith Mother
1 Joey Mr. Smith Father
2 Susie Ms. Wilson Aunt
3 Billy Ms. Jones Mother
3 Billy Mr. Steves Father