How to structure a table(s)?

Seeking for experienced advice on structurizing the database table. I have a 7 day money balance table for each user, where the balances are calculated on-the-fly, but the incomings/outgoings are stored in the db.

Example: https://pp.userapi.com/c855620/v855620022/868b5/mS5orfhQu84.jpg

How would you structure the database for this type of tabular data? Below I share three of my approaches.

One table for all information: https://pp.userapi.com/c855620/v855620022/868bc/4BZhoDSZ2FA.jpg
Separate tables for different movements: https://pp.userapi.com/c855620/v855620022/868c3/RXYebVZ3QTk.jpg
Two tables: one for days, one for movements: https://pp.userapi.com/c855620/v855620022/868d3/Si3Brz5Ul0k.jpg

Any best practices is very warmly welcomed and appreciated! Thanks!

narcisonunez21

6 years ago

Level 18

You can go with the 3rd approach creating two separates tables but to avoid having a days table (days will be always be the same) you can do this:

1- For day_id use the \Carbon\Carbon::now()->dayOfWeek (0 = Sunday)

You can do it in eloquent automatically like this:

public function setDayIdAttribute ($value) {
   $this->attributes['day_id'] = $value?: \Carbon\Carbon::now()->dayOfWeek;
}

In that way you can forget about passing the day id because eloquent will always set the current day number by default

Please or to participate in this conversation.