Best design for a table using variant data
Hello, I am currently facing a design where a table (virtually) needs to store attributes of a topic (related table). The attributes can be user defined, i.e. not known at development type and depend on other factors. Each attributes value can be one of different types (int, int64, double, string) and may have constraints like min, max or length (string). Thus the data type would be modeled as variant in some programming languages, but this is not an option in SQL (beside the MS SQLserver sql_variant extension). So, how to simulate this in SQL. Basically there would be one table describing the attributes type (Type identifier, min/max,.) and one table for the values itself. The design I am currently thinking of would be to make exactly these two tables, with the attributes having a Dataype column and iMin,iMax for integer, i64Min, i64Max, fMin,fMax for double , sMinLen, sMaxLen for strings. The same applies to the value table which will have iValue, i64Value, fValue, string columns to hold the actual data. Now the columns will be filled according to the data type, columns not matching the type will be NULL. This means that each row in the table will have virtual space for any data type which violates database normalization. However it seems to me to be the best deal for performance and data space as NULL takes virtually no room (4/8 bytes in total for a number of fields in some circumstances) and requires no complex queries. One could also imagine to have the constraints moved to a separate table and interpreted according to the data type. Also a table for each type could be imagined but this will make the queries very complicated working against performance. Note: The model has to work for huge databases Anybody has a better alternative ?
Re: Best design for a table using variant data
You don't specify how many different types (including min/max values) you expect to be using. If you expect to end up with a few hundred, then you should perhaps consider using an ENUM or SET column directly in the data table. / Carsten On 10.08.2012 10:51, Gaston Gloesener wrote: Hello, I am currently facing a design where a table (virtually) needs to store attributes of a topic (related table). The attributes can be user defined, i.e. not known at development type and depend on other factors. Each attributes value can be one of different types (int, int64, double, string) and may have constraints like min, max or length (string). Thus the data type would be modeled as variant in some programming languages, but this is not an option in SQL (beside the MS SQLserver sql_variant extension). So, how to simulate this in SQL. Basically there would be one table describing the attributes type (Type identifier, min/max,.) and one table for the values itself. The design I am currently thinking of would be to make exactly these two tables, with the attributes having a Dataype column and iMin,iMax for integer, i64Min, i64Max, fMin,fMax for double , sMinLen, sMaxLen for strings. The same applies to the value table which will have iValue, i64Value, fValue, string columns to hold the actual data. Now the columns will be filled according to the data type, columns not matching the type will be NULL. This means that each row in the table will have virtual space for any data type which violates database normalization. However it seems to me to be the best deal for performance and data space as NULL takes virtually no room (4/8 bytes in total for a number of fields in some circumstances) and requires no complex queries. One could also imagine to have the constraints moved to a separate table and interpreted according to the data type. Also a table for each type could be imagined but this will make the queries very complicated working against performance. Note: The model has to work for huge databases Anybody has a better alternative ? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
MySQL Connector/ODBC 5.2.1 beta has been released
Dear MySQL users, MySQL Connector/ODBC 5.2.1 beta, a new version of the ODBC driver for the MySQL database management system, is available for download. The 5.2 driver series is suitable for use with any MySQL server version since 4.1. (It will not work with 4.0 or earlier releases.) This new version of MySQL Connector/ODBC combines both existing driver series, the ANSI driver is an upgrade to the 3.51 series, and the Unicode driver is an upgrade to the 5.1 series. The beta release is available now, in source and binary form for a number of platforms, from our download pages at http://dev.mysql.com/downloads/connector/odbc/5.2.html and mirror sites. Note that not all mirror sites may be up to date at this point in time, so if you can't find this version on some mirror, please try again later or choose another download site. For information on installing, please see the documentation at http://dev.mysql.com/doc/refman/5.5/en/connector-odbc-installation.html We welcome and appreciate your feedback, bug reports, bug fixes, patches, etc.: https://wikis.oracle.com/display/mysql/Contributing Enjoy! The MySQL Connectors team at Oracle == Functionality added or changed: * Driver prepares statements on server * Support of INOUT and OUT parameters(WL#6116) Bugs fixed: * Long table names crash OBDC driver. (Bug# 14085211/65200) * Close proximity issue when handling OS signlals in myodbc (Bug# 14303803) * String data right truncated error is not always correct (Bug# 14285620) Built against the MySQL server 5.5.27 client library. -- Kent Boortz, Release Staff engineer Oracle, The MySQL Team Mobile: +46 76 77 69 049 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
