I am having a dillema.
I am not sure which is the best way to organize data in my database.
I am dealing with a very large number of objects (may be millions of them) which could be neatly arranged in some sort of a Tree.
I thought of three possible approaches:
1. Use a MAIN TABLE for the objects themselves and a large number of TABLEs for those object's fields which allow only a finite, discrete set of string values. Parentage should be explicit or inferred from the properties.
2. Use only one TABLE and store there only the object's ID and PARENT plus the fields which add something new to the parent. All the other fields (which are identical to the parent's ones) should be NULLs and their values copied from the parent when necessary.
3. Same as 2, but only one property field per object, its kind (like COLUMN) being inferred by the depth of the Node in the Tree.
Whew! And now, finally, the question:
Which approach (or other if you can think of) would be better suited for:
* Smaller database (on disk)
* Faster access (retrieval of data)
* Contructing / parsing / modifying the Tree structure.
* Ease of implementation, clarity of underlying code, standards...
(their relative importance in this order)
And... Does it matter what type of database I use (firebird, mysql, microsoft sql express, etc)?
And... Using stored PROCEDUREs makes it any faster (compared to parametrized queries)?
Anything else...?
Thank you.
Submit an article or tip