Re: Defining proper relationships between similar entities

From: Neo <neo55592_at_hotmail.com>
Date: 22 Jan 2007 08:23:00 -0800
Message-ID: <1169482980.356654.288340_at_a75g2000cwd.googlegroups.com>

> Consider a scenario in which various users must be stored in a
> relational database. Said users have different access levels. In
> addition, each type of user has different information associated with
> their account. Here's an example:

Below implements the example in an experimental db that manages data having any structure (lists, tables, tables, graphs, networks, etc) in a systematic manner (ie NULL-less, non-redundant, resilient to new data requirements, etc). It stores a customer named John Smith, supported by Mary; and admin Bob. A query finds customers from state of illinois that were supported by some supportPerson at phone# 123.111-2222 between 2006 and 2007.

(new 'firstName)
(new 'lastName)
(new 'username)
(new 'password)
(new 'phone#)
(new 'email)

(new 'address)
(new 'street)
(new 'city)
(new 'state)
(new 'zip)

(new 'john 'person 'customer)
(set (it) firstName 'john)
(set (it) lastName 'smith)
(set+ john username 'bigJohn)
(set+ john password 'mars)
(set john address (block

                               (new)
                               (set address instance (it))
                               (set+ (it) street (set '123 'main 'st))
                               (set+ (it) city 'chicago)
                               (set+ (it) state 'illinois)
                               (set+ (it) zip '34567)
                               (it)))

(set+ john phone# '777.888-9999)
(set+ john email 'johnathan&mickey.com)

(new 'mary 'person 'supportPerson)
(set+ mary username 'littleMary)
(set+ mary password 'chocolate)
(set+ mary phone# '123.456-7890)
(set+ mary phone# '123.111-2222)
(set+ mary email 'mary&yahoo.com)

(new 'bob 'person 'adminstrator)
(set+ bob username 'bigBob)
(set+ bob password 'shipCargo)
(set+ bob phone# '123.111-2222)
(set+ bob email 'biggerBod&msn.com)
(set+ bob email 'biggestBob&yahoo.com)

(new 'support 'verb)
(new '20060000 'dateTime)
(new '20060101.1135 'dateTime)
(new '20060101.1148 'dateTime)
(new '20070000 'dateTime)
(new '20070115.1355 'dateTime)
(new '20070115.1405 'dateTime)

(set mary support john from 20060101.1135 to 20060101.1148)
(set mary support john from 20070115.1355 to 20070115.1405)

(; Get a customer

   from state of illinois
   that were supported by some at phone# 123.111-2222    between 2006 and 2007)
(; Gets john)
(& (get customer instance *)

    (get * address (get * state illinois))
    (get (& (get supportPerson instance *)
               (get * phone# 123.111-2222))
           support *
           from (>= (get dateTime instance *) 20060000)
           to (< (get dateTime instance *) 20070000)

    )
)

For another example with persons with highly varied attributes, see www.dbfordummies.com/example/ex020.asp Received on Mon Jan 22 2007 - 17:23:00 CET