I am writing a project for, let’s say, a company that maintains multiple real estate projects throughout the city. In this way, they have:
- Real Estate Project (typically a group of apartment houses with single main address).
- Single apartment house in the group (project), that has it’s own address.
- Single apartment inside the house.
So, the address of #1 will be Main street – 28, address of #2 will be Main street – 28, building 4 and address of #3 will be Main street – 28, building 4, apt. 12.
The problem that they also have some single houses that are actually as #1 and #2 at the same time (like Another street – 123 (and lots of apartments)).
I am struggling to design a DB schema for this and have multiple variants:
- Each address, not regarding if it is apt, house or project address, will be stored in own DB row (like plain text):
|1||Main street – 28|
|2||Main street – 28 building 1|
|3||Main street – 28 building 1 apt. 12|
- Each address will be stored in DB with relations, with separate tables for main addresses (Main street – 28), address extensions (…building 4) and apt.numbers (apt.12) and they will have relations with each other (like parent_id).
The problem is that since there might not be the middle section (no main address extension, like
building 4), I’m afraid the last variant won’t work as expected, since each apartment will be attached to it.
Would really appreciate your help!
Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.
Found the way this could be achieved. I used one of many laravel packages for Nested Set Model structure implementation. In this case, I will have the tree-like model in database, where there are parents and descendants.
In this way, I can refer to every parent element even if I have retrieved only the deepest child element of the root. This also allows multiple operations, like counting up/down (parents/children), as well as building an actual visualized tree model.
There are multiple Laravel packages for achieving this.