Re: database design for nesting items

On Sun, 30 May 2004 21:22:46 +0200 in comp.databases.theory, "Yaron Lapidot" <ylapidot_at_netvision.net.il> wrote:

>Hello all
>i've to design a database that hold infomation about documents
>each document could be a stand alone or a parent of other document/s
>each document have his own attributes and a uniqe key.
>
>my problem is how to hold the attributes of the data.
>because documents will be create dynamicly i can define a table whice will
>hold the attributes data and be
>varchar data type that can hold all kind of data(a lot of casting work)
>or define a table for each data type i use ?

If you want to be easily able to query combinations of different attributes and retrieve all related documents, put all the document attributes into a single table and make each document attribute a row in the attribute table: document, attribute name, attribute value. If you split the attributes across different tables, you will have to write code to generate queries across tables and use different combinations of tables for querying about different attributes of a single document; perhaps requiring a table relating document attributes to table names, never a sign of a good database design. If you push the document attribute information into data in a row, you can easily write queries relating to any combinations of attributes.

>eventualy i design a tree of documents 'that each doc can hold his own
>attributes .

Trees are best represented as binary relations in tables holding: parent document, child document.

-- 
Thanks. Take care, Brian Inglis 	Calgary, Alberta, Canada

Brian.Inglis_at_CSi.com 	(Brian dot Inglis at SystematicSw dot ab dot ca)
    fake address		use address above to reply