Imagine a coiffeur, with his services:
+----+-----------------+--------+
| id | name | amount |
+----+-----------------+--------+
| 1 | hair cut | 40 |
| 2 | shampoo | 10 |
| 3 | french manicure | 90 |
+----+-----------------+--------+
Imagine a table of transactions:
+----+------------+------------------+
| id | id_service | date_transaction |
+----+------------+------------------+
| 1 | 1 | 01/01/2015 |
| 2 | 1 | 02/01/2015 |
| 3 | 1 | 02/01/2015 |
| 4 | 2 | 02/01/2015 |
| 5 | 2 | 02/01/2015 |
| 6 | 1 | 06/01/2015 |
| 7 | 1 | 06/01/2015 |
| 8 | 3 | 06/01/2015 |
| 9 | 2 | 06/01/2015 |
| 10 | 2 | 10/01/2015 |
| 11 | 1 | 10/01/2015 |
| 12 | 3 | 10/01/2015 |
| 13 | 1 | 10/01/2015 |
| 14 | 2 | 10/01/2015 |
| 15 | 2 | 11/01/2015 |
| 16 | 1 | 16/01/2015 |
+----+------------+------------------+
With a simple INNER JOIN we can read, for example, the SUM of a specific day.
But our coiffeur is no technology addicted and tomorrow he will change every service.
So, to prevent this drama, how I must setup my webapp?
1) I repeat service.name and amount.name every time in the table of transactions? So, I don’t use the INNER JOIN?
1a) I could lose also the possibility to GROUP BY, without numerics ID. And I cannot use them from service table because service change (or, worst) is deleted…
2) I need to block the user to edit/delete his service(s)?
3) What your consideration?
8
The question should not be whether or not you need to use inner joins.
The design is flawed. The flaw is that the transaction entity is missing a property, i.e., your transaction table is missing a column to record the amount of the operation. The other flaw is that the “amount” of a service is really its “price”.
- Add an
amountcolumn to thetransactiontable. - Rename the
amountcolumn in theservicetable toprice. - When a transaction is added, you set the amount to the corresponding price value.
- User should be allowed to change the price of any service at any time
- User should only be allowed to change the name of a service if it has no transactions.
- Uses should only be allowed to delete a service if it has no transactions.
- Changes in service prices don’t affect the amount of old transactions.
If you want to make sure there is an established list of services (a table to inner join) then you need to manage the changing of this table without breaking your joins for historical records.
- Use a soft-delete. Add a field to the list of services called: IsDeleted. Check it off when you no longer offer the service. The ID will remain. I would probably make this a date field to know when the delete occurred for archival purposes.
- Use a start date field and an end date field on the service table to indicate when these services were offered. These fields could also be posted for known price changes in the future.
All of this will take more coding to handle the logic of what services are available when.
Your transaction table should not have to rely on the services table.
That’s because the moment a transaction is done, the relation to the services table is lost. Why?
In the services table apparently the name of your service or the amount of the service can change. In a historical transaction table, the name and amount will be what it is at the time you enter it into the transaction table and won’t change if there’s a changes in the services table.
That’s why amount shouldn’t be in the services table either but should have its’ own table. That way you can keep a historical price record. And no, you don’t make references to that in your transaction table either. Because a price can be anything. (like let’s say you come in, but because you also bring your kid, the kid gets a haircut for half price. The transactions table has to have freedom to receive any custom amount.).
Ofcourse you can keep a reference to your services table, so if your client decides to change “super haircut” to “awesome haircut” which is the same service under another name, that way you can still group.