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.