From: Carsten Pedersen
Date: August 10 2012 1:32pm
Subject: Re: Best design for a table using variant data
List-Archive: http://lists.mysql.com/mysql/227982
Message-Id: <50250D78.40900@bitbybit.dk>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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 ?
>
>
>
>
>
>