Asset Management Database Design

Question

I am currently busy implementing a basic asset management system. It will not be very difficult. Just something to keep track of any assets with that name, serial number, part number and type, etc. However, the problem is that I want to include books as well. Unfortunately, books have a completely different structure than ordinary assets (for example, title, authors, isbn codes, etc.).

I would like to get some insight from the community about which design they consider the best. Include books in asset management (and if so, what the database project should look like), or I just have to write a completely separate independent library module (perhaps with some functions for exporting the book to the asset management system [with fewer other fields]).

Thanks!

EDIT: Something else that is possible is to make the capture screen dynamic so that the user can specify fields and values. Then it can be saved as XML in the database. But it would not be my preferred way to do this.

EDIT 2: I forgot to mention that I am very connected with the technologies that I can use. These are MySQL, GWT, Hibernate, and Spring transactions (without Spring).

Answer 1

One approach might be to use a document-style no-sql database (e.g. Mongo) to store assets. Thus, each other type of asset can easily have its own set of fields, without requiring additional tables, etc.

Basically what I present is pseudo code similar to:

class Asset { int AssetNumber; int AssetType; string Description; // etc. } class BookAsset : Asset { // book-specific fields } class ElectronicsAsset : Asset { // electronics-specific fields } // etc. 

Thus, additional types of assets may simply be additional derived classes. Then, each asset will be recorded in the document database as a separate document and retrieved by its asset number (or searched based on its fields, etc.) or name or, nevertheless, it is stored.

This will give you a quick and easy system with the flexibility you are likely to want as you track additional assets or additional information about existing assets.

Edit based on your edit: Custom fields should work fine with this. You can configure it as a kind of dictionary of keys / values ​​on the object or even just add fields to the object itself if you use a more dynamic language. The "underlying asset" will consist of fields that are absolutely necessary, the rest can be more weakly defined, conditionally necessary, specified by the user, etc.

Answer 2

It makes sense to separate the general concept of an asset from the specifics of each type of asset that you want to include. As a rule, this will take the form of a table of fixed assets, with different tables for each individual type of asset that you want to include, that is, books, equipment, furniture. The structure may look like this:

 Asset(AssetId, Description, Comments) HardwareAsset(HardwareAssetId, AssetId, SerialNumber, ...) BookAsset(BookAssetId, AssetId, ISBN, Publisher, Author, ...) 

Where the AssetId in HardwareAsset and BookAsset is the foreign key of the Asset table. This way you can track different assets and group them together when it matters.

---

EDIT: Alternatively, you can create a key value table to store values ​​for individual objects, which might look like this:

 AssetValue(AssetValueId, AssetId, Key, Value) 

However, this is a cumbersome solution, which when you save search fields quickly inflates your database. To fix the problem, you can limit the size of the field depending on your requirements. I do not suggest serializing the dictionary within one field, as this will inflate your database even more.

Answer 3

From technological limitations, I would suggest retaining individual modules.

Answer 4

Yes, on the main table you can indicate what type of asset it has. So if it is a book asset, by which their foreign key can associate it with the elements of the book. that you will not spend space on those assets that do not have these items.