Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

2 Answers 2

up vote 3 down vote accepted

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
share|improve this answer
add comment

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

share|improve this answer
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
2  
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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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