I am designing a real estate database to keep records of plots (lands). There are many aspects to it like sale deeds, buyers, sellers, etc. But specifically, the bit where I've got stuck and need your help is how to model the following requirement:
- An existing plot can be subdivided into one or more plots. And two or more plots can be merged into one.
How to keep records of these changes and also be able to backtrack in history.
Plot has the following attributes:
- plot_number (primary key)
- area
- is_vacant
- classification
If you can provide an example with a SQL code or a diagram that would be greatly appreciated.
Let me know if you have any questions or need clarification.
Any help would be greatly appreciated.
Thanks
