Relational Databases: how to design one

 A relational database is an information storage method where records are stored in relation to each other. Records get tied to other records by relationships. It is necessary to have relationships between records primarily so that data access can be optimized for storage and access.

A quick example would be a record relation between a user and address. A user record would need to be related to addresses by a ‘many’ type relation. Thus: a ‘user’ ‘has_many’ ‘addresses’. Otherwise, every time a user acquires an address, you would have to replicate the entire user record just so the address section can be different for the new address. You would have two user records instead of just associating new values to the user.

All database relationships are implemented using these basic relationships (using ‘user’ as an example record)
 
 has_one
A ‘user’ has_one ‘identity’

An ‘identity’ belongs_to a ‘user’

A ‘user’ has_many ‘devices’

A ‘user’ has_many_and_belongs_to ‘groups’

A ‘group’ has_many_and_belongs_to ‘users’

The has_many_&_belongs_to is useful when the relationship is direct and no inference can be drawn between the two. As mentioned within the groups for example. However, say you needed to create a relationship between a doctor and a patient? The relationship would work but we would then miss out on extracting a meaningful relationship; appointment. We can say; a doctor can have many patients through appointments. This is where pivot relationships shine. As an example:

 A ‘user’ has_many ‘users’ as ‘friends’

 Now you have a virtual ‘friend’ attached to the actual record user. You can use this information to query for example; how many ‘friends’ a ‘user’ has. 

has_many :: through
A ‘user’ has_many ‘cars’ through ‘logbook’

This automatically creates a ‘logbook’ record every time a new ‘car’ record is created in relevance to a ‘user’. The ‘logbook’ record will automatically belong_to a ‘user’ and the ‘car’.

 With this, one can design a diagram illustrating how entities relate to each other. These diagrams can then be interpreted into code and that’s the basic philosophy behind database design.

From all the above; we notice that most relationships are like conversational English. They remain useful to keeping data slim and efficient to fetch. Also, additional information can be inferred from such relationships. The inverse is also true, an English statement can be reversed back into database definitions. For example;

‘You can have many cups in a house have rented from the owner as a tenant’ is a complete definition of relationships to be set up as the above examples.

This will yield;
 
 ‘user’ has_many ‘cups’

 ‘cup’ belongs_to ‘user’

 ‘user’ has_many ‘houses’ through ‘tenant’

 ‘house’ belongs_to ‘user’ through ‘tenant’

 ‘owner’ has_many ‘houses’ as ‘landlord’

 ‘house’ belongs_to ‘owner’

 ‘owner’ has_many ‘tenants’

This ‘yield’ is not set in stone. You can re-define it to fit purpose.