0

I am dealing with the design of a database in Flask connected to Postgresql. I have 2 Tables Reservation and Device which are related through a many-to-many relationship Table ReservationItem as follows:

| Reservation |     | Device |     | ReservationItem |     
| ----------- |     | ------ |     | --------------- |
| id_res      |     | id_dev |     | res_id (FK/PK)  |
| etc...      |     | etc..  |     | dev_id (FK/PK)  |
                                   | created_at      |
                                   | status          |

Where dev_id and res_id are foreign keys and make up the composite primary key for the table. The columns created_at and status where originally conceived to track the history of the development of each Reservation-Device status.

Example Someone reserves 3 Devices (respectively with id_dev's 1 - 2 - 3) on the 1st of January 2021 hence I would create 1 Reservation entry (id_res 1) and 3 ReservationItem entry with status "booked".

ReservationItem
| --------------------------------------|
| res_id | dev_id | created_at | status |
| ------------------------------------- |
|   1    |   1    | 2021-01-01 | booked |
|   1    |   2    | 2021-01-01 | booked |
|   1    |   3    | 2021-01-01 | booked |

On the 2nd of January the client returns the Device.id = 1 so I would create a fourth entry in the ReservationItem Table where the only updated fields are created_at and status, so that I could track where the devices are.

| --------------------------------------- |
| res_id | dev_id | created_at | status   |
| --------------------------------------- |
|   1    |   1    | 2021-01-01 | booked   |
|  ...   |  ...   |    ...     |    ...   |
|   1    |   1    | 2021-01-02 | returned |

Which basically weaken the uniqueness of the composite key (res_id,dev_id).

So I thought: Should I created another table lets say History to track these updates?

These would be my new models...

| ReservationItem |     | History       |   
| --------------- |     | ------------- |   
| id_assoc (PK)   |     | id_hist (PK)  | 
| res_id (FK)     |     | assoc_id (FK) | 
| dev_id (FK)     |     | created_at    | 
|                 |     | status        |

I would change the ReservationItem Table so that res_id are dev_id are not primary keys anymore. I would move the created_at and status into the History table and I would add the column id_assoc and use it as primary key, so that I can reference it from the History table.

I've been looking around and it seems that using one column as primary key in a many to many relationship is not ideal.

How would you design the relationships otherwise? Is there any tool that Flask offers?

EDIT After reading this post, which suggests to audit database table and write logs to track changed entries (or operations on databases) I found this article which suggests how to implement audit logs in Flask. But why wouldn't my solution work (or lets say "isn't ideal")?

thank you!

ddgg
  • 145
  • 1
  • 10
  • "it seems that adding a primary key to a many to many relationship is not ideal". First : the primary key is just mandatory for any kind of table in postgres. Second : the primary key can be composite, ie (res_id, dev_id) so no need of the id_assoc column in the table ReservationTime. Third ; your first attempt with 3 tables sounds more optimzed than the second with the History table which doesn't provide any added value in your case. – Edouard Nov 29 '21 at 22:04
  • I have never heard of a composite primary key. I will take a look into it . Thank you!. About your third point: I was considering the new design because, if you think about it, one entry in the ReservationItem Table can have many "updates" that can be tracked over time (different status steps such as "delivered", "booked", "returned", etc..). I was wondering if things would get messy without a fourth table... – ddgg Nov 29 '21 at 22:59
  • The fourth table is a good idea when you need additional columns in table ReservationItem (independent from the History of the association) and additional columns in table History. In the basic example given of your question, the fourth table has no real added value. – Edouard Nov 30 '21 at 08:19
  • @EdouardH. ok that is great! meaning that if I want to track who did a change (for example a User registered in another table) - meaning adding a column "user_id" to the "History" table - would you drop the composite primary key and assign an id to each ReservationItem entry? – ddgg Nov 30 '21 at 10:05
  • both solutions are possible with pros and cons in both cases, either keep id_assoc as primary key of table ReservationItem and assoc_id in table History refering to id_assoc as a foreign key, or remove id_assoc and keep (res_id, dev_id) as composite primary key of table ReservationItem, and (id_res, id_dev) added to table History as a foreign key refering to table ReservationItem (id_res, id_dev) – Edouard Nov 30 '21 at 10:51

0 Answers0