Implementing a c/c++ style union as a column in MySQL

Question

Friends,

I have a strange need and cannot think my way through the problem. The great and mighty Google is of little help due to keyword recycling (as you'll see). Can you help?

What I want to do is store data of multiple types in a single column in MySQL.

This is the database equivalent to a C union (and if you search for MySQL and Union, you obviously get a whole bunch of stuff on the UNION keyword in SQL).

[Contrived and simplified case follows] So, let us say that we have people - who have names - and STORMTROOPERS - who have TK numbers. You cannot have BOTH a NAME and a TK number. You're either BOB SMITH -or- TK409.

In C I could express this as a union, like so:

union {
        char * name;
        int tkNo;
      } EmperialPersonnelRecord;

This makes it so that I am either storing a pointer to a char array or an ID in the type EmperialPersonnelRecord, but not both.

I am looking for a MySQL equivalent on a column. My column would store either an int, double, or varchar(255) (or whatever combination). But would only take up the space of the largest element.

Is this possible?

(of course anything is possible given enough time, money and will - I mean is it possible if I am poor, lazy and on a deadline... aka "out of the box")

Answer 1

No, there is no 'union' column type. But you can create a column that is big enough to hold the largest element and another column that works as type indicator. I.e.

... data VARCHAR(15), data_type enum('int','double','char')...

Answer 2

As a1ex07 said, you CAN do it by storing string representation. But if you are worried about space, storing real values in several NULLable columns will probably save more space.

Alternately, create ancillary tables and normalize, e.g.

Your want:

TABLE1
|id|name_or_TK#|

Your can do:

TABLE1
|id|name|TK|

or you can do

TABLE1
|id|ST_or_human_flag|other columns common to humans and stormtroopers

TABLE2 - Names_of_humans
|id|name|

TABLE3 - TKs_of_STs
|id|TK|

Answer 3

C unions are a pretty bit-twiddly way of handling this problem.

What you have there is a polymorphic data type. So, one way to solve the problem is to switch to an object-oriented DB, or one that is dynamically typed, like some of the "NoSQL" ones.

If you have to stay with the current relational DB, you can do the standard thing which is to build some kind of ORM -- object-relational mapper -- to do the translation. One way is to put the common ("base class") fields in the main table, along with a type column, then use the type column to select which "leaf class" table holds the additional fields. For instance:

table Employee
    field id int
    field emp_type enum('human', 'stormtrooper')
    field salary int
    field division_id int
    field manager_id int

table HumanEmployee
    field emp_id int
    field name string

table StormtrooperEmployee
    field emp_id int
    field tk_number int

That is, the *Employee tables are tied back to the base Employee table by employee ID.

Answer 4

I think you should have 2 different columns and store data accordingly, when retrieving you can cast and append them together something like col1 + col2 as full_name