>
> > > How do you plan on dealing with situations where there is a row,
> > > and a
> > > column, but no value that ought to be stored in
> > > the intersetion of the
> > > given row and the given column?
>
> > I am sorry, you would need to give an example.
> > I was not able to understand exactly what the situation would be.
>
> I was trying to get an example from you. Let me be very distinct in my
> question:
> Are all 900 columns identical? meaning:
> same data type
> (the columns are all CHAR, NUMBER, BLOB, other???)
> same domain of data
> (the column are all character, representing DNA sequences)
> same semantics
> (the columns are all CHAR, representing Colors, which are a coloring
> of a map)
>
All 900 columns will be VARCHAR types.
Even though also numbers are stored. -> Numbers are also stored as
VARCHARs.
> > If there is 900 columns per one Item. Then all of those columns would
> > have a value.
>
> The same value?
There are different kinds of values. All are stored as VARCHARs.
> > Next Item could have only 100 columns (or attributes) and then only
> > those 100 columns would have a value.
>
> Aha! Now a new clue. If there can be variations, are the attributes
> really groups of attributes? These groups could be normalized in
> different tables.
Each item can have e.g. 900 columns (or attributes) or 100 columns.
Group of 900 attributes form one type of an Item, or group of 100
attributes form an other type of Item. I call these groups of
attributes as types of Items.
*But*, there are many of those types of Items. Maybe even tens. So I
am not sure would it be nice to make a separate table for each of the
types of Items. (If that would be suggested.)
New types of Items can be formed and then a new table would be needed
to be created for those too.
Scalability would not be so good, if there would be separate tables
for all types of Items.
And the performance problem (related to the query) might still exist
with those tables too.
But yes, it would be an alternative still to have separate tables for
all types of Items.
--
> I still have to question the need for being able to query on any
> column.
A query to fetched particular value (or values) from a column would
take tens of seconds or minute (or something like that) with one
RDBMS, if index is not be used on the column (on all of the 900
columns).
That would be too slow for my application.
That might be the requirement for the performance of the query.
So it looks like the index would be needed, at least with table
structure 3, which I tried. The query took too long. The query would
be too slow with table structure alternative 1 too (one row and e.g.
900 columns).
I mean this kind of a query:
First fetch a set of Item IDs.
Then fetch some value from one column from those Item IDs, which were
found.
( Tested with alternative 3 for table structures. All columns on
separate rows. )
Even if all types of Items would have own tables, the query might be
too slow and an index might be needed on all of the columns still. And
I would like to avoid tables with e.g. 900 columns, even if indexes
would not be needed on the columns.
> Knowing you data
> and database features helps, but actually running tests is the way to
> confirm performance meets the goal.
>
Yes, it seems I just need to test.
-
> but I really see little progress without knowing
> what the data looks like.
Yes, this seems to be all I can write.
-------------------------------------------------
I have tried to describe as much about the data as I can.
SUMMARY
I will write summary once still:
Functionality: monitor and analyze Items.
New type of query needed: fetch set of Item IDs, which has value x on
column y and which belongs to group z and the type of the Item ID
should be w.
There are:
- Groups. Different (types of) Items can belong to one group.
- Items. These belong to Groups.
- Attributes which belong to the Items.
Each Item has also a type. One Item can have e.g. 900 attributes
or 100 attributes depending on the type. There are tens of types of
Items, so there can be Items, which have an amount of 200, 300,
400, ..., etc. attributes.
Attributes of the Items are stored as VARCHARs.
Requirement: I need to fetch some particular value or values from one
of 900 columns at a time. The query must be done rather in seconds
(not in tens of seconds or in minutes, or hours). Values are fetched
only inside a group and inside the type of Items.
So query is made on these Item IDs
(table structure alternative 3, all columns on separate rows):
select Item ID from table1 where group ID = ? and Item type = ?;
And only those Item IDs are chosen, which have value x on column y:
select Item ID from table2 where column_ID = ? and value = ?;
Requirement: Performance of insertions need to be "good enough".
Size of the DB is maybe not so crucial, but it would be quite big with
some table structures.
-----------
Table structure alternative #3 once more:
Table 1:
Columns: Item ID, Item name, group ID
Index 1.1 on (Item ID)
Index 1.2 on (group ID) (or something like this)
Table 2:
Columns: Item ID, column ID, value
Index 2.1 on (Item ID)
Index 2.2 on (value, column ID).
Thank you!
All comments are welcome still of course!
|
