Re: Schema

Owen Cliffe <occ@cs.bath.ac.uk> writes:

> For external interfaces mainly, and for base-line support in apps etc.
> say for example a contact manager had a GUI form which only edited the
> Basic types it is important that they have a way of referring to those
> types when they get round to adding stuff to the database, its just
> easier from an app-programmers point of view to have an alias for say
> address, the separation thing is just to ensure that the 2 classes of
> contact type can be grouped into two sets of contiguous IDs)
>
> likewise a lot of external interfaces (say synchronization, or for
> example a VCard transmitter) have to deal with the basic types in a
> different way to any user-defined types, and the user-defined types may
> become meaning less.
>
> its easier to "SELECT contact_value from contacts where type=ADDRESS"
> (where address is a defined constant) than always having to do
>
> SELECT contact_value from contacts,contact_types where contacts.type_id
> = contact_types.id and contact_types ="address".
>
> Its just a way of saying: You can safely use these types and here is an
> easy way of reffering to them.

Ok, now I see where you're going with this. I don't mind requiring a bit more
work on the part of the developer if it provides more flexibility for the
user. Although I don't really see a need to reserve a block of
contact_type_ids for the pre-defined types, I also don't really see a good
reason not to do so. Consider it agreed, on my part.

CREATE SEQUENCE t_contact_type_seq MINVALUE 1000;
CREATE VIEW contact_type_seq AS
  SELECT nextval('t_contact_type_seq') AS nextval;

which allows you to insert a new contact type without worrying about
overwriting the reserved values:

INSERT INTO contact_type
  VALUES (contact_type_seq.nextval, 'Airphone number')

(Are sequences available in SQLite? I doubt it, but this same thing could be
implemented manually as well.)

Derrell
Received on Thu Feb 14 11:10:01 2002