Re: Schema

From my limited experience, calendar items are hard to databaseize as
they are more directory-oriented (hint hint) but here is an attempt at a
schema for caledar items/events (inspired by iCal) (brutalize at will):
(i assume global ID for ical/VCF will be implemented
elsewere/automatically)

--------------------------------------------------------------
table events:

id: int #event id
summary: string #basic description of event
details:string #detailed desc. of event
location: string #location of event
start: date ("YYYYMMDDHHMMSS") #basic start time of event (i.e. time of
                                # start of first/only instance)
end: date #basic end time of event (time of end of # first/only
instance)
class: [PRIVATE|PUBLIC|GROUP] #accesibility of event data #
[0:public,1:private,>1:groupid] or whatever
rrule:string #repetition rule (see evolution/ # vcal format or
similar)(basically # string describing repetition of event) #
*yes this is left undefined because i # don;t know how to define it*
------------------------------------------------------------
groups table:

table evGroups:
id: int # group ID (>1)
name: string # group name
details: string # details

------------------------------------------------------------
alarms table:

table evAlarms:
id: int # unique id
evid: int # event ID
adv: int # amount of time before event in
                                        # secs
alType: enum [BEEP|RUN_PROG|etc...] # what to do
param:string # parameter string to alType
------------------------------------------------------------
event-contact relation table:

table evPeople:
id: int # unique id
evid:int # event id
cond:int # contactid
note:string # association note

looking at this it seems obvious that we are going to have to provide a
support library (to deal with repeated events/dates/name handling, and
to put the typedefs in, as well as datestring support) at least, for
apps. so that might be a good idea to start proposals for :

my impressions for the initial functionality will be:
- provide local wrappers for string types (i.e. dbstring -> date and
  V.V.)
- provide abstraction and handling for repeated events (i.e parse rrule
strings and generate rrule strings from data structures)
- any other stuff anybody thinks is important.

I have no idea to what extent libical does this kind of thing already
though... but i imagine most of the functionality can be borrowed from
there.

owen

On Wed, 2002-02-13 at 23:54, Owen Cliffe wrote:
> cool good start,
>
> some ideas:
>
> 1) s/company/org/ or orangi[z|s]ation ("org" degates the nondeterminstic
> spelling) (not all of us work for companies)
>
> 2) if you are going to have forename (for that is what i assume fn is )
> and ln (lastname?) you should probably have title, middle name etc.
> Evolution has a neat little name-parser somewhere bound to the
> add-contacts dialog, which breaks a display name into one or more of
> - prefix
> - given (forename)
> - additional (middle(s))
> - family name(s)
> - suffix. (Jr. |Sr.|O.B.E. etc)
> which are the basic e-card name elements.
>
> you could just store this as a full string (and give everybody a pain in
> the butt.) or as separate fields.
>
> as Derrell says for the contacts an external table could be used, which
> is logical as the search relation is almost always in the
> person->contact direction (although this is more general work for johnny
> programmer) I don't agree with Derrell's typing table (contact_types)
> this is too meta for this kind of app, a simple enum on the contact
> types would do (i.e. a internal type to describe the different contact
> types) there can't be that many after all that would obviously save
> quite a lot of space as 95+% of my contacts at the moment have just one
> entry of one of the possible types) GUI editors could just present the
> user with the basic types in a form or whatever.
>
> owen
>
>
>
>
> On Wed, 2002-02-13 at 23:01, Robert Mibus wrote:
> > Just to get the ball rolling, here's a starting point for a contact
> > manager:
> >
> > id int primary key
> > fn text
> > ln text
> > company text
> > phone_mobile_home text
> > phone_mobile_work text
> > phone_home text
> > phone_work text
> > fax_home text
> > fax_work text
> > email_home text
> > email_work text
> >
> > This is from an sql statement to create a table, so it's:
> > [name] [type] [options]
> >
> > mibus
> >
> > --
> > Robert Mibus <mibus@bigpond.com>
> > Which came first.. the revision or the bug?
> >
> >
> >
>
> _______________________________________________
> GPE mailing list
> GPE@handhelds.org
> http://handhelds.org/mailman/listinfo/gpe
>
>
Received on Wed Feb 13 16:53:16 2002