Mysql – Storing two integers as a decimal

database-designdecimalMySQL

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?

Best Answer

  1. 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.