DB layout - solutions needed
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/68311http://www.proSoft-Edv.de -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: DB layout - solutions needed
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_classINT 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/68311http://www.proSoft-Edv.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: DB layout - solutions needed
Thanks Chris, Will you have an object hierarchy of any type to speak of? no hierarchy, they're all roots. 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). Maybe i've miss-explained a bit: objects wasn't ment like what objects means in OOP. My objects are collective agreements in fact. They may be stored that way, but: Currently i have to store all data in dbase-DBFs accessing them with Visual Objects(http://cavo.com), but it will be stored in MySQL DBs in a few month. That's why for me any solution has to be relational. CREATE TABLE funky_object_store ( object_serial INT AUTO_INCREMENT, object_classINT NOT NULL, object_data BLOB, PRIMARY KEY(object_serial), INDEX(object_class) ); I need to be able to query the DB for a specific character- istic. So serializing the objects as OOP-objects into BLOBs isn't suitable for me. Regards, TomH -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: DB layout - solutions needed
Hi Brad, create table entity ( ent_id number primary key, ent_namevarchar(50) ); create table nameval ( nv_id number primary key, nv_ent_id number, nv_name varchar(50), nv_val varchar(50) ); That is what i prefered if i knew i have to store only char-values. The question is, if type-casting all values to char is more or less performant than wasting space by lots of emtpy fields. Currently i think, having a third table and one for each datatype will lead to some more joins and selects for getting the data, but it may be the clearest solution: create table entity ( ent_id number primary key, ent_namevarchar(50) ); create table valtypes ( vt_id number primary key, vt_name varchar(50) vt_type enum( int, longint ) ); create table vals_int ( v_idnumber primary key, vt_id number v_val int ); create table vals_char ( v_idnumber primary key, vt_id number v_val varchar(100) ); ...and one more table for each other datatype used. It's just that each more select takes more time:( Thanks, TomH -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
