CODEWITHSUNDEEP
oracle11g
Patrick
Patrick

Reputation: 2577

Is this database design typical/advantageous

I know this might be opinion based, but I'm looking at someone's database and I'm curious as to whether or not this is a common technique and what the advantages/disadvantages are, in the spirit of becoming better.

Essentially, they have a set of tables that store monetary transactions by month/year vs one table (as I personally would have made). So it looks like this:

So I'd have made something like:

PaymentTable
receiptNumber | Tendered | date 
      001     |    100   | 01/12/2017
      002     |    200   | 02/10/2017
      003     |    300   | 03/7/2017

Whereas they made it 

 Payment0117
    receiptNumber | Tendered | date 
          001     |    100   | 01/12/2017

 Payment0217
    receiptNumber | Tendered | date 
          002     |    200   | 02/10/2017

 Payment0317
    receiptNumber | Tendered | date 
          003     |    300   | 03/7/2017

I'll say up front, it's been a pain to work with. They don't have a view set up with all the tables, so if I was looking for receipt 002, I'd have to check every table until I found it. But I'm wondering, are there performance or other advantages to this approach since each table would have fewer rows? They go back to 2006, so there are 10 years*12 months = 120+ of these tables...

Upvotes: 1

Views: 23

Answers (1)

Daniel
Daniel

Reputation: 426

Looks like someone used table names to store information. That is clearly not conform to a entity-relationship-approach.

A table should represent an entity, like payments, customers etc ... The Payment-Month is a property of the payment, and a redundant one as it is contained in the payment date.

I can imagine that was done to get smaller tables and maybe isolate the active accounting month with a lot of writes from the old month, which would be basically read-only. Anyway there are better ways to deal with that.

If the month is a regular argument for reports, that should be properly indexed.

If you have a lot of writes only int the current month, and a lot of reads for only the old month, maybe a buffer-table for writes of the current day/month etc. could make sense or a BI-table to service reporting.

Upvotes: 1

Related Questions