Different Types for the Same Column

Question

My database stores version numbers; however, they come in two formats: major.minor.build (e.g. 8.2.0, 12.0.1) and dates (e.g. YY-MM-DD). I had thought of two solutions:

+---+---+-----+-----------+ +-----+-----+-----+-----+ +-----+--------+
|...|...|id   |versionType| |id   |major|minor|build| |id   |date    |
|---+---+-----+-----------| |-----+-----+-----+-----| |-----+--------|
|...|...|12345|0          | |12345|0    |1    |2    | |21432|12-04-05|
|---+---+-----+-----------| +-----+-----+-----+-----+ +-----+--------+
|...|...|21432|1          |
+---+---+-----+-----------+

or

+---+---+-----+-----+-----+-----+--------+
|...|...|id   |major|minor|build|date    |
|---+---+-----+-----+-----+-----+--------|
|...|...|12345|0    |1    |2    |null    |
|---+---+-----+-----+-----+-----+--------+
|...|...|21432|null |null |null |12-04-05|
+---+---+-----+-----+-----+-----+--------+

Neither of these look particularly efficient: the first requires a join across two tables just to get a version number, while the second wastes twice as much space per version entry compared to the first. Alternatively, I could just store the value in some amount of bits in a column then interpret that on the client side, but I'm hoping that there's some standard practice for this situation that I've overlooked.

Is there a proper way to store two different types of data for the same 'column' in a relational database?

Answer 1

Is your situation one where you have different distinct kinds of versioned object, where one kind of versioning is using dates, and another kind of versioning is using version numbers, or is your situation one where the same kind of object's version is referenced both using dates and using version numbers ?

In the first case, don't bother with creating such an artificial table that doesn't serve any useful purpose. You need to create tables only if they solve a business problem that really exists, and the translation from version date to version number or vice-versa is one that doesn't exist in this situation. And even if it arises later on, then you can still ...

In the second case, define a table like the one in your second option, but :

WITHOUT all those stupid meaningless ID's. Just leave the four columns maj/min/bld/date. And DONT MAKE ANY OF THEM NULLABLE. Define two keys : maj/min/bld and date. Register a row for each new build, recording the creation (/activation/whatever ...) date of the build. Use the maj/min/bld construct as the version indicator in whatever table describes the versioned object you are managing, and whenever a request comes in in which the version reference is done using a date, resolve this to a version number through a query on your 4-column table.