database design help

[Jacek Trzmiel]

Hi,

Brian Blais wrote:
> I am trying to design a system for people to submit a series of documents to a
> project.  I want users to have the ability to submit updates to any documents, so
> that there should be a history (or sequence) for each document.
[...]
> project1:
>    document 1, document 1a, document 1b
>    document 2
>    document 3, document 3a
> 
> project 2:
>    document 4, document 4a
> 
> etc...
> 
> I want to be able to query the history of any single document, so I can get a list of
> document 1, 1a, and 1b.  or document 3  and 3a, etc...
> 
> So I have something like this (omitting a few lines, for clarity) to set up the
> record structure:
> 
>      users.create(('name',str),
>              ('email',str))
> 
>      project.create( ('description',str),
>              ('label',str),
>              ('creation_date',date),
>              ('submitter',users))
> 
>      documents.create(('filename',str),
>              ('submit',date),
>              ('submitter',users),
>              ('type',str),
>              ('project',project))

Split document into separate document and revision concept, i.e. 
something like this:

      documents.create(('type',str),
              ('project',project))

      revisions.create(('filename',str),
              ('submit',date),
              ('submitter',users),
              ('document',documents))

(I'm not sure how you will use 'type', so maybe it should belong to 
revision.)

Best regards,
Jacek.