Storing two integers as a decimal

Question

What are the downsides to storing two integers as a decimal?

I am storing asset details in tables, each asset type has its own table (each asset is very different) and using another table to define the asset tables, so each asset table has an integer id and each asset also has an integer id.

I have two different scenarios where this could be handy:

1. have an "audit" table that stores information like: this user did this to that item

2. someone is assigned to work on this asset of this type. I was thinking of storing it like assetType.assetID so asset type 5 and id 99 would be decimal 5.99

I would very rarely need to select based on 5.99, I would just query the record that stores the 5.99, then split it and use a function to go to table 5 record 99.

I can't tie the assetID to a specific table; assetType is the id of an entry in a table referencing the asset tables (defines things like table name, primary key column and the like) so it already seems like I wouldn't be able to use foreign key constraints either way.

There are a lot of asset tables, like asset_tmv and asset_backflow. An asset is assigned a type by what table it is in, as the data to be stored for each asset varies greatly.

I realise I could achieve this using 2 integer fields. What I am wondering is: What would the downsides be?

Answer 1

2. someone is assigned to work on this asset of this type. I was thinking of storing it like assetType.assetID so asset type 5 and id 99 would be decimal 5.99

But the user wasn't working on assetID=99, the user was working on assetID=990. I'm sure MySQL will match 5.99 to both.

You should just store the two different values in two different columns.

Answer 2

_{Note: In order to expose the advantages of handling each discrete data point via its dedicated (logical-level) column, this answer is based exclusively on the following excerpt:}

_{…someone is assigned to work on this asset of this type. I was thinking of storing it like assetType.assetID so asset type 5 and id 99 would be decimal 5.99…}

_{In order to analyze the scenario in full (every entity type, property and association of relevance), a description of all the informational requirements of the particular business domain you are working on is indispensable; in other words, one has to know the applicable business rules, which at the time of this edit, have not yet been provided.}

_{If you want to supply all the information required, with all the relevant details involved, just let me know and I would be glad to share my take on the scenario as a whole.}

---

You clarified via comments that your intention is building a relational database, so retaining two distinct pieces of information in the same column would imply multiple downsides, e.g.:

• The logical level of abstraction of the database under consideration would not be representing the corresponding conceptual schema with the required precision.

• Logical-level constraints (value validation) would be much harder (or impossible) to set up declaratively (one has to resort to archaic, sub-optimal, procedural methods to protect data integrity).

• Data manipulation at the logical level becomes needlessly cumbersome.

• The work that the database management system has to perform at the physical level in order to optimize or even support the logical operations becomes superfluously more complex.

• Database usage, in general, becomes unnecessarily more difficult since the database structure and constraints would be puzzling for DBAs, application programmers and, more importantly, end users (in certain environments, there are power users who access databases without application program assistance).

The Asset example

Let us use the scenario about the Asset entity type as an example. So, starting the analysis from the conceptual level, one can say that:

• An Asset is primarily distinguished by its Id
• An Asset is alternatively distinguished by exactly-one Name
• An Asset is classified by exactly-one Type.
• A Type classifies zero-one-or-more Assets

Therefore, the informational requirement of that particular business domain entails keeping track of two distinct data points, i.e., Asset.Id and Asset.Type, each of which carries an exact meaning; hence, at this stage it is very clear that each of them:

• entails a specific set of constraints; and
• has the potential of undergoing manipulation individually.

Subsequently one can represent said conceptual elements by way of the following logical-level SQL-DDL layout:

CREATE TABLE asset_type ( 
    asset_type_id INT      NOT NULL,
    name          CHAR(30) NOT NULL,
    --
    CONSTRAINT asset_type_PK PRIMARY KEY (asset_type_id),
    CONSTRAINT asset_type_AK UNIQUE      (name) -- ALTERNATE KEY.
); 

CREATE TABLE asset ( 
    asset_id      INT      NOT NULL,
    name          CHAR(30) NOT NULL,
    asset_type_id INT      NOT NULL,
    -- 
    CONSTRAINT asset_PK PRIMARY KEY               (asset_id),
    CONSTRAINT asset_AK UNIQUE                    (name), -- ALTERNATE KEY.
    CONSTRAINT asset_to_asset_type_FK FOREIGN KEY (asset_type_id)
        REFERENCES asset_type (asset_type_id)
);

As demonstrated, each datum would be kept in its dedicated column, and with this arrangement:

• One can constrain asset.asset_id declaratively as the PRIMARY KEY of the asset table, which prevents the possibility of accepting duplicated values in such a column. The database management system programmers have worked on optimizing the related physical-level processes carried out “under the hood” for years.

• Being declared as a PRIMARY KEY, the asset.asset_id column can now be “used as a target” from one ore more FOREIGN KEY constraints, in case it is necessary.

• It is very easy to guarantee referential integrity by way of declaring a FOREIGN KEY constraint on the asset.asset_type_id column, making a reference to asset_type.asset_type_id.

• You do not have to resort to functions accessing column value sub-parts (as in the case of the non-atomic combined column) in data manipulation operations, be it INSERTs, SELECTs, UPDATEs, DELETEs or combinations thereof. Functions would (1) increase processing time at the physical level and (2) would reduce code readability.

• The (a) logical layout reflects the (b) conceptual schema with precision, which aids all the interested parties when interpreting data structure and result sets. Writing the queries themselves is much simpler because each column stands for a clear and individual piece of information.

• At the physical level, having a separate asset.asset_type_id column permits fixing one (or more) single- or multi-column index(es) for this table, which can speed up execution of logical queries that, e.g., include the column as a condition in the WHERE clause.

Thus, in view of all of the above, my main point is: Each data point of interest should be represented at the logical level by its corresponding —properly typed and properly constrained— individual column.

I recommend re-analyzing thoroughly the scenario on your own, starting at (i) the conceptual level and then proceeding with (ii) the corresponding logical design, since that method facilitates obtaining an exact mapping and you will be able to avoid uncalled-for hacks straightforwardly.

Audit trail

You mention in the question that you are interested in enabling some kind of audit trail for the database at issue so, in light of that, I would suggest following an approach like the one I expose in this answer and in this other answer (both about temporal capabilities) with the pertinent adaptations, of course.

Answer 3

_{Community wiki answer please edit to improve if you can}

From an indexing and execution standpoint, you're introducing the likelihood for execution plans that cannot use the seeking capability of indexes. That means poor performance on updates and selects. Seems like a lot of extra work for no payoff.