[Zope-DB] Data modelling question

[garry saddington]

On Sunday 21 September 2003 21:36, Charlie Clark wrote:
> Dear list,
>
> I have the following problem - a glossary of terms where some but not all
> terms are synomous.
>
> My relational model is currently: id, term, description because definitions
> were exclusive but who says things have to stay like that? Customers don't
> understand relation models and the CONSTRAINTS they impose ;-)
>
> Possible solutions:
> 1) allow two synonymous terms to have two distinct but identical
> definitions 2) allow a reference from one term to another "see XYZ..."
> 3) allow both synonmyms to refer to the same definition.
>
> Here are my initial thoughts:
> 1) Can be done without programming. Rejected because it violates redundancy
> and might cause nasty problems later even merely as a precedent
> 2) Avoids redundancy but breaks data / programming separation layers as the
> description field has to be checked to see if it contains a link.
> Administration would be fairly easy.
> 3) How this 1 -> 1 / m -> 1 modelled? New table simply for descriptions
> with a reference to a description's id from the term's table? or an
> intersect table mapping n terms to n descriptions? Programming
> administration is tricky.
>
> My current inclination is to go with 3) as it keeps to model clean so stops
> the slide down the slippery slope. But as this cannot be a unique problem
> I'd appreciate opinions and suggestions.
>
> Thanx
>
> Charlie

You could just use the one table but then it seems that you would have 
redundant data and much functionality would be difficult to implement. i 
would decompose to a three relation model and implement in a RDMS such as 
Postgresql:

tblsynonym (synonym_id (pk), synonym)
tblassign (assign_id (pk), synonym_id (fk references tblsynonym), 
               description_id (fk references tbldescriptions)
tbldescriptions (description_id (pk), description)	

where pk=primary key and fk=foreign key 

you can add in any extra fields that you may wish to have.
HTH
regards
garry             
> _______________________________________________
> Zope-DB mailing list
> Zope-DB at zope.org
> http://mail.zope.org/mailman/listinfo/zope-db