I'm currently working on desiging my app's DB.
I have several entities in the system, such as:
Each bank account has one or more owners.
Each money transaction is between 2 bank accounts.
My DB should answer simple questions such as
"All people who are living in Paris"
"All money transactions in the last week"
but also "relational" questions such as
"All bank accounts of persons that are older than 40 years"
"All money transactions in London"
I thought modeling each entity as an index, and also add new index: relations. Now i have 2 options:
use ids -> "source_id", "target_id", "relation properties"
In this option i will filter on the entity itself (person older than 40), and then i'll search on the ids. It can be a lot of data, and a lot of time.
denormalize -ALL- the searchable data on the relation:
"source_person_id", "source_person_name", "source_person_age"...
This is very expensive option, and each update of an entity in the system will cause search all the relations and update it also
Is there any other way? If no, which of the above options are better?
I'll be happy to hear some opinions before starting developing