Re: Help with Design and Obtaining of Data

> If anybody can suggest anything that would help along these lines,
> a solution, or pointers on how to handle this it would be greatly
> appreciated.

In summary, you have entities with varied attributes.

Existing method: Store in one table.
Adv: Simplicity. Simple queries.
Disadv: Many attributes dont apply to each entity. Lots of NULLs. Unnormalized data. Possibly waste of memory. Possiblity of running out of columns.

Proposed method: Store entities in one table. Store attributes/values in second table.
Adv: Each entity only has the desired attributes. Possibly less storage space. Much fewer columns.
Disadv: SELECT queries are more complex. Possibly slower retreival.

I would suggest going with the most normalized/NULL-less solution, that will meet you other needs (ie performance). I won't worry about the complexity of queries so much as they only need to be written once and their complexity can be hidden behind wrapper functions.

Alternatively, there exist other novel databases, ie dbd, that store data using nodes instead of tables. Dbd allows each entity to have different attributes and each attribute can have multiple values. Ddb automatically normalizes the data and the queries are fairly simple. The below dbd queries are similar to those in original post.

(get (. 'john 'smith) dob *)
(get (. 'john 'smith) home *)
(get (. 'john 'smith) mobile *)

(get (. 'mary 'ann) dob *)
(get (. 'mary 'ann) fax *)

The following two examples store/query "Persons with Varied Attributes"
www.dbfordummies.com/example/ex015.asp
www.dbfordummies.com/example/ex020.asp

> In our real database, we have over 450 of these attributes, and it has
> now got to the point where it is a big performance hit - the table has
> nearly 500 columns.

If you would like to email me a portion of the data, I can enter it in dbd for comparison. Received on Wed Jul 25 2007 - 19:21:18 CEST