How to represent variable data types?

#11

On 2009-01-06, bukzor <workitharder (AT) gmail (DOT) com> wrote:

Quote:

On Jan 6, 2:55Â*am, Jasen Betts <ja... (AT) xnet (DOT) co.nz> wrote:
On 2009-01-05, bukzor <workithar... (AT) gmail (DOT) com> wrote:
EAV is (apparently) exactly what I'm doing. There is a huge number of
value types (attributes) that don't always apply to the case at hand
(entities). I thought I could possibly simplify my design and
concurrently reduce the database footprint and increase speed by using
a single Value table rather than one for each type.

if the values are not used in queries serialise them and stash them in
a blob, if they are used they should be in a column with an index.
(could be a 2 coulmn table for ones that are infrequently used with
the other column referencing a public key record in the trials table,

Someone wanted an example of my four queries: (Keep in mind this is
simplified for the sake of discussion.)

SELECT TrialName, MetricName, MetricValue
FROM trials, names, values_d AS v
WHERE
Â* Â* v.nameid = names.id
Â* Â* AND v.trialid = trials.id
Â* Â* AND names.type = 'd'

I was thinking that the querys were retreiving single values
and that were only 4 types of values
and supposing that series of LEFT OUTER JOINs could combine
the queries,

now I see thant there are more than 4 types fo values (else why name
them) and that there are possibly many of each type,

Sorry when I said 'types' i was using the programming-language
meaning. There are many 'kinds' of values (as indicated by the
MetricName) but they fall into only four types (d/i/s/t). You've
obviously figured this out already, but I wanted to clarify.

you could still combine your queries with a union,

but I think really you need to reconsider your database design.

Using a blob to store my values is an option I hadn't considered. It
might be workable since none of my queries need to test against the
values.
My only concern is speed/space. How much bigger is a blob '1' than an
int '1'?

dunno for mysql.

Quote:

Also, the blob destroys alignment of the table (since it's
variable-length) which is supposed to slow down seeks, but I don't
know by how much.

Splitting the blob into a separate table would solve

Quote:

this. My Values table then becomes just three foreign keys: extremely
compact. Using a blob also means that I can start storing .gif's or
python objects as values, which has been a planned feature for a long
time. I like it! Thanks!

I was thinking that a test can have many values and that all the values could
be put into a single blob. (or if using python's inbuilt serialize
into a text field (if mysql has unbounded string fileds, else into a blob))

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

How to represent variable data types?

comp.databases comp.databases

Re: How to represent variable data types? - 01-07-2009 , 08:35 PM