Relationship problem between entities

Question

I'm not sure how I should think regarding my database design. I have made this database schema below. And I'm trying to think about the relationship between the entities. I have read through online that many-to-many should be avoided as much as possible and it makes sense. But in my case here, can it be avoided and how? The idea is that there should exists only "unique" items in the WalletStorage and WalletPlatform table that one or many can be owned by a Wallet.

For example, my reasoning is that a Wallet can have many WalletPlatform and WalletStorage that it owns. Also, a WalletPlatform and a WalletStorage can not just be part of one Wallet. But soon as I implement this in for example Hibernate for a one-to-many relationship between Wallet and WalletPlatform and WalletStorage it attaches a column in the WalletPlatform and WalletStorage for each row with the wallet_id. It probably does exactly what it supposed to do, but I'm clearly missunderstanding something here.

In the wallet_storage table the structure is this:

id    name     wallet_id

In the wallet_platform table:

id    name     description    wallet_id

Answer 1

The schema is inconsistent

There is a discrepancy between the designed diagram and the foreign keys:

• you designed in your diagram a one-to-many relationship for Wallet with WalletPlatform, WalletStorage and CryptoCurrency (i.e. one wallet could have several platforms, storage or currencies), but
• the foreign keys added in the schema to the Wallet implement a many-to-one in the opposite direction (i.e. a wallet can have only one platform, storage or currency, but those could be associated with several wallets).

Is it possible that you have a mismatch in the Hibernate definition of the associations/relations? In case of doubt, don't hesitate to update your question with the annotations you've used.

And a missing many-to-many relationship?

I’m not very knowledgeable of the wallet world, but I am very familiar with currencies. In this respect, I wonder if the relationship between Wallet and CryptoCurrency, in both the designed diagram and the foreign keys is correct:

• according to your diagram, a Wallet can be associated with several CryptoCurrency rows, but each crypto-currency can only be associated to a single wallet. For example, only one wallet could use bitcoins and all the other wallets have to use something else??
• according to the generated FK, a wallet could be associated to a single currency, but a currency could be used by several wallets.

I understand that a wallet should be able to hold several crypto-currencies, and a crypto-currency can be held by several wallets. That's many-to-many. There is absolutely no valid reason to avoid many-to-many if this is the reality of the domain.

Many-to-many implies the use of an association table (also called junction table, intersect table or bridge table):

• hibernate is perfectly able to generate it (see for example this SO answer).
• if you prefer, you can manually break down the many-to-many in your diagram with two one-to-many relations with an intermediate entity (e.g.CurrencyInWallet).

Hint: The breakdown with an additional entity makes sense if it reveals a well identified domain entity (e.g an “assignment”), and even more if it could have own attributes. But often, this is not the case and the additional entity would seems somewhat artificial; it usually start with the difficulty to find an entity name. In this case, prefer to keep your diagram simple and just use a junction table without promoting it to a full-blown entity