Hi,
Here is the scenario:
1. I have data for ~ 1M thingies (not Larry Walls kind!); each thingie
has what one might call demographics (unique id,
name,location,dates,physical parameters,fixed characteristics) and it
also has variable information. By that, I mean each thingie may have a
variable number of, shall we say, children, but lots of children; each
thingie can have up to 51 children, each with a name,height and weight.
The children's names come from a fixed universe of about 500 names.
There are actually 5 of these types of variable characteristics, but
presumably, I can extend the idea from the one. So, let us pretend there
is just the one.
2. What do I want to do in a reasonably efficient manner?
a. Extract by unique id all children belonging to that unique id
with their height and weight
nothing like select * from master_demographics m, children c
where m.uniqueid=c.uniqueid and
name1 is not null and name2 is not
null...and name51 is not null
b. Extract across unique id's all heights and weights belonging to
a given name. Yes, Frank belonging
to uniqueid1 is the same Frank belonging to uniqueid499, even
though the height and weight are
different. I might want to contour Frank's weight across many
uniqueid's, for example.
3. Thoughts so far:
master table (PK uniqueid)with demographics (children/nochildren is
important, so a column for that purpose is included)
children table (PK uniqueid)with a column for each possible name of
the universe of 500 names plus height and weight--hmm, 1500 columns; I
don't like that.
4. So, if I have made any sense, any help is much appreciated.
Thank you,
Don
