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 object
s (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 object
s themselves and a large number of TABLE
s 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 NULL
s 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 PROCEDURE
s makes it any faster (compared to parametrized queries)?
Anything else...?
Thank you.