Contesting banknote database structure

Question

I have a banknote allocation system where contestation, replica and/or rejoinder is allowed, which is the best way to store in the database?

1. Create three tables (contestacao, replica, treplica)
2. Create a table with the 3 fields
3. Create a table with the field tipo

Answer 1

Depends on what you want.

Create three tables (contestation, replica, treplica)

This allows for the insertion of various contests, replicas and rejoinders for each note (which I believe is not your goal). The control by the system is greater, since there are 3 tables, and if it is the case to bring the records in a single query, can force the system to make use of Unions, which is not good for performance. Great for cases where it is interesting to access only one table of each type at a time.

Create a table with the 3 fields

For the business scope, I believe it is the most appropriate, considering that each note can have only one contestation, a replica and a rejoinder. It is also the simplest to obtain and update, but it does not serve for multiple records of challenges, replicas and rejoinders.

Create a table with the type field

This case is interesting when you want to save in number of queries and minimize the amount of joins used by the system. It is also for the case that your system allows a variable amount of challenges, replicas and rejoinders. You may need to add an index by type, which makes the table larger in the database.