[Zope-DB] Data modelling question
garry at joydiv.fsnet.co.uk
Sun Sep 21 17:16:47 EDT 2003
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.
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
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.
> Zope-DB mailing list
> Zope-DB at zope.org
More information about the Zope-DB