Database Design – Solving Relationship Problems Between Entities

database-designdesignrelationships

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

Best Answer

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

Related Solutions

Php – Laravel relationships and database schema

Since you didn't follow the convention of Eloquent you have to type it manually and it's easy just specify the second and third arguments into the hasOne or other functions of ORM obj.

i.e:

class User extends Eloquent {
   public function phone()
   {
      return $this->hasOne('Phone','id','phone_id');
   }

}

Where the second argument is the column name of the first argument (the table you want to make relation with, in this case Phone) and the third argument is the column name in this model User

Version Control – Managing Database Contents

The technique I have normally used is to save the complete record, with an end_timestamp field. There is a business rule that only one row can have a null end_timestamp, and this is of course the currently active content.

If you adopt this system, I strongly recommend you add an index or constraint to enforce the rule. This is easy with Oracle, as a unique index can contain one and only one null. Other databases may be more of a problem. Having the database enforce the rule will keep your code honest.

You are quite correct that lots of small changes will create bloat, but you need to trade this off against code and reporting simplicity.