"
MIME-Version: 1.0
Content-Type: text/plain
Content-Transfer-Encoding: 8bit
Hmm....
Will you have an object hierarchy of any type to speak of? If so, you
should be able to factor quite a few things out, but then you're going
to have the problem that objects further down the tree will take longer
to store and retrieve, regardless of table type.
Which language are you looking at building this funky thing in? If it's
Java or C++, you might want to look at persistant object stores that are
specifically for this purpose (for Java, you might consider Prevailer /
Previlence, for C++ try ColdStore).
Finally, XML may be what you need. As we all know, the longest-surviving
transactional table-type in MySQL is BDB, built on top of the stalwart
embedded key-value database technology from Sleepycat. Their XML product
may be just what you're looking for.
As a general rule, attempting to put this sort of data on top of the
relational model rules out high performance. Even though the "big three"
(Oracle 9i, MS SQL Server 2000, DB2 V8) all claim to handle XML data
well, the performance is pathetic compared to what we're used to.
Additionally, Object-Relational storage can be simplified (for many
purposes) to having 1-to-1 relationships between "classes" (read
tables).
The best option (if you really want to use MySQL might look like this):
CREATE TABLE funky_object_store (
object_serial INT AUTO_INCREMENT,
object_class INT NOT NULL,
object_data BLOB,
PRIMARY KEY(object_serial),
INDEX(object_class)
);
Regarding object_class, if you've got less than 255 different classes,
using an ENUM field may be more efficient (but may not be - MySQL
currently has a shortcoming that means it compares ENUM values based on
their string representations, not their index in the enumeration).
Otherwise, using InnoDB tables with foreign keys might make you feel
safe with the object_class specifiers being in a seperate table.
Hope this helps!
Regards,
Chris
On Fri, 2004-01-16 at 20:53, Tom Horstmann wrote:
> Hi all,
>
> i want to store an unfixed number of object-characteristics
> of an unfixed datatype for 1-8000 objects. Data is mostly
> read, rarely written. Is there an quite optimal table-structure
> for that?
>
> If i use one table for all characteristics i need as many
> columns as there are datatypes used. Only one column of those
> will be filled for each row. A lot of wasted space:(
>
> Casting the values to a fixed datatype will give the easiest
> structure at a high cost of performance:(
>
> Another way was to have one table for each datatype a
> characteristic may have. Then i will need almost one other table
> just for finding the table a value is stored in:(
>
> Are there any real solutions? Which would you prefer?
>
>
> TIA,
>
> TomH
>
> --
> PROSOFT EDV-Lösungen GmbH & Co. KG phone: +49 941 / 78 88 7 - 121
> Ladehofstraße 28, D-93049 Regensburg cellphone: +49 174 / 41 94 97 0
> Geschäftsführer: Axel-Wilhelm Wegmann [EMAIL PROTECTED]
> AG Regensburg HRA 6608 USt.183/68311 http://www.proSoft-Edv.de
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
