I am reworking an app to make it more flexible for future additions. I attached a basic relationship table layout to think through this. Here is what I am thinking and just curious if this seems to be the most efficient way to handle this.
The main purpose I am breaking this is up is so that I can create a slightly more modified and advanced search functions. To clarify this is an app to manage real estate transactions. So an example of the use would be if I had a single property that was rented out every year by same Lessor but different Lessee’s. Here is an idea of what data each table would contain.
Property: (PIN #, Address, Property Type, Squar Footage, Lot Size). Mostly items that are unlikely to change so no need to have multiple occurences of this data over a number of years and/or transactions and if they do just want one source to prevent redundancy.
Contacts: While Lessor will likely the stay the same across most transactions, in my example at least, the Lessee’s will change yearly in my example across various transactions. There are other situations but that is the best example where this would be most beneficial.
Transaction: (List Price, Sold/Rented Price, MLS #, Specfic Transaction Dates etc). Each transaction will have different values but only need one property but will contain multiple contacts as well (Seller, Buyer, Lessor, Lessee, Brokers, Attorneys etc).
TransactionContacts: Will allow me to add multiple contacts to a transaction and assign a type.
The key reason for breaking this down, as I am thinking through this is so I can use the TransactionContacts to search Contacts and find all transactions or properties associated with that contact as well as search Transactions or even Property and find all Contacts associated with those while still allowing the ability to add multiple contacts.
Additionally this will allow me to add other functionality like tasks, logs etc that will allow me to search by contacts, transaction and/or property separately for different reasons. Example would be if I pull up transactions it would display all tasks associated with that transaction while in the transaction layout and the same would go for contacts. Bascially they are separate entities as far as searchable features.
I was also curious if I wanted to add the property to the Contacts via related ID, would I just create a new occurence. For example, a seller is going to often have the same property address as the transaction. Would prefer to add the address to contacts from the property database so if I need to make a change like maybe there was a sqft mistake, I could modify it once across the whole app in different scenarios from one source, (property, contacts, transaction etc).
Sorry if this is a lot of info but just trying to figure out if this is the most efficient way to do this. Would like to create this and use it on FMS to access from FMP on various devices keeping one central piece of data.
Feel free to fully critique my thought process and any input would be very helpful.