Re: relational db design question

> I am trying to create a recipe database, in which I would like to
> associate each recipe with a reference (the reference can be a book,
> newspaper, person, magazine, etc...)

Solutions to such problems can be difficult in RMDBs. Values in a table's column can only reference a single table. They cannot refer to rows in multiple tables (ie T_Person, T_Book, T_Magazine, etc). One solution is to point to a table such as T_Thing which will hold persons, books, magazines, etc. Then the challenge is to accommodate attributes which are likely to be different for each type of thing. You can either have unused fields with NULLs or you can store each thing's attributes in a separate table (ie T_ThingAttribValue).

Below is a solution using an experimental db based closely on set theory and lambda calculus. It models four recipes. First authored directly by john and mary. Second and fourth from pages of a book authored by john. The third recipe, authored by mary from pg 106 of a magazine. Sample queries near end find recipes based on authors.

(new 'source)
(new 'author)

(new 'john 'person)
(new 'mary 'person)

(new (set 'joy 'of 'cooking) 'book)
(set (it) author john)
(new 'p24 'page)
(set (. 'joy 'of 'cooking) page (it))
(new 'p48 'page)
(set (. 'joy 'of 'cooking) page (it))

(new (set 'cooking 'corner) 'magazine)
(set+ (it) date '20070101)
(new 'p106 'page)
(set (it) author mary)
(set (. 'cooking 'corner) page (it))

(new 'recipe1 'recipe)
(set recipe1 source john)
(set recipe1 source mary)

(new 'recipe2 'recipe)
(set recipe2

     source
     (& (get (. 'joy 'of 'cooking) page *)
         (get * name 'p24)))

(new 'recipe3 'recipe)
(set recipe3

     source
     (& (get (. 'cooking 'corner) page *)
         (get * name 'p106)))

(new 'recipe4 'recipe)
(set recipe4

     source
     (& (get (. 'joy 'of 'cooking) page *)
         (get * name 'p48)))

(; Get all recipes sourced by john and mary)
(; Gets recipe1)
(& (get recipe instance *)

    (get * source john)
    (get * source mary))

(; Get all recipes from pages of something authored by john)
(; Gets recipe2 & 4)
(& (get recipe instance *)

    (get * source (get (get * author john) page *)))

(; Get all recipes either

   sourced by mary
   or sourced from pages authored by mary)
(; Gets recipe1 & 3)
(| (get * source mary)

   (& (get recipe instance *)

       (get * source (& (get page instance *)
                               (get * author mary)))))