I have a database the keeps track of rental contracts and should be able to calculate indexation of the rent at some point in the year. There is a table with the CONTRACT-INFO (Including a couple of parameters that affect the indexation-ratio), and I made a related table that will list various YEARS and their correpondig rent-amounts.
Now the indexation options are varied, to say the least. With four different parameters that affect the indexation-ratio and a large numbder of years it comes to about 600 different of indexation-figures. So I made a separate table with INDEXATION-FIGURES.
I would like to look up the right pair of figures, so I can devide one by the other to get the rent-increase-factor.
So far the table with the INDEXATION-FIGURES is unrelated, but I did a web-search on this and conclude that is not the way to go. I thought, there is no relationship... ...or actually, the truth is, there is a relationship from every YEAR-record to every INDEXATION-FIGURES-record. For any YEAR, any INDEXATION should be "lookuppable".
I also found out that many-to-many relationships need to have an INTERMEDIATE table, with linking records.
- Is this the right way to go?
- And am I right that for every new YEAR-record I start, I will have to run a script that populates 600 related records in the INTERMEDIATE table with their F-ID's pointing at all these INDEXATION-FIGURES?
Regarding the last one, I don't think so.
Is any one available to get me going? Thanks in advance.