Geek asks and answers

Different types for the same column

My database stores version numbers; however, they are presented in two formats: major.minor.build (for example, 8.2.0, 12.0.1) and dates (for example, YY-MM-DD). I was thinking 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|
+---+---+-----+-----+-----+-----+--------+

None of them looks particularly effective: the first requires the union of two tables to get the version number, and the second - twice as much space for recording the version as compared to the first. Alternatively, I could just store the value in a number of bits in a column and then interpret it on the client side, but I hope there is some standard practice for this situation that I have missed.

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

+3
6

, . , , CHAR (10), (, ..).

, , , ?

.

0

, , , , , , ?

, - . , , , - , . , ...

, , , :

. maj/min/bld/date. . : maj/min/bld date. , (/activation/whatever...) . maj/min/bld , , , , , , .

+1

. , , , , ( ) , ( ).

0

, int/bigint. : - , - , , , unixtimestamp - ( 1000), (1000). = major * 1000 * 1000 + minor * 1000 + build

0

, - , , . 3- 1 16 ( 4 ) , Oracle. () , , nvl (to_char (date), major || '.' || minor || '.' || build) varchar, .

0

, . , , varchar.

, , , , . , , 2. ? ? , , , .

, , ( ), , .

If, on the other hand, some objects have a version date, some version number, and sometimes both, I would still consider your second solution. If you really want a model with a normalized uber, you can consider creating separate version_date and version_number tables, which have a 1: 1 ratio to the object table. And you still need these associations. Again, there is no reason to worry about this: the databases are good at joining, this is what they did to succeed.

0
source

More articles:


All Articles