3

I have a table where users will enter transactions on a weekly basis, these transactions will either be single items or will be recurring i.e. rent would be recurring and should be the same for every month. How should I store this recurring item?

We thought just add in 12 new entries so it atleast covers the year (The users will want to look a few months into the future) but then the problem is that if the cost changes, we have to update all following entries

Our other option was to create another table which would store the key of the original with the dates or day of the month that it needs to repeat on?

Is there a better solution than these?

3

You shouldn't try to do too many things with one data store.

If you have a table for transactions, then just keep transactions in that table. Don't mix in scheduled future transactions with current transactions. Actual (historical) transactions will have different attributes (columns) than future, scheduled transactions.

Keep the recurring/scheduled transactions (like rent) in a separate table. When the rent is paid, create a new record in the (actual) transaction table.

The scheduled transaction table could have a totally different structure. Instead of keeping a record for each expected transaction, you could keep one record with a date range and recurrence fields. This would save having to update multiple records when the rent price changes, for example. Consider something like this for your scheduled/recurring transaction table:

Scheduled_Transaction:=

  • CustomerID
  • StartDate
  • EndDate
  • Recurrence (monthly, weekly, every two weeks, - whatever you need)
  • Amount
-5

According to the Concept of Normalisation there should be no Redundant set of records

  • 1
    The "Concept of Normalization" is a great guideline however in the real world exceptions are made on a regular basis. – Kenneth Fisher Aug 30 '13 at 13:36
  • 3
    Typically, you would want to provide more detail in your answer than just "because the concept says so". Adding detail about why this is important will help the OP understand the reasons and thereby help them in future situations. – Max Vernon Aug 30 '13 at 15:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.