I need to keep track record in postgresql

Question

I need to keep track record in postgresql

Navigation

#1 by (0 votes)

0

I am doing a database design in postgresql and I need to take the history of the patients, which can go through several categories, the complicated thing is that all the time I must know what were the data that the patient had in each category , it may even be that the patient can go from chronic to severe and then go back to chronic. All the time I must have stored that data. Any idea other than duplicating the patient's record with the new category and hiding it from the other registers? I also thought about creating a denormalized history table, I do not know, I need help. The drawing was with Paint;) Sorry for the ugly. Greetings.

sql postgresql pgadmin

asked by Alberto Blanco Cala 12.12.2017 в 20:48

source

1 answer

Multiple iFrames and "104: Connection reset by peer" How to read $ execute-fetch (PDO :: FETCH_ASSOC)?

score 0 · Answer 1

You can use a new table with a field that refers to the patient table, which MUST have only one record per patient, one field for the status, another for the annotations and another for the date in which you register this event:

id - SERIAL
id_pac - INT (FK - > patient.idpac)
status - INT [0 = chronic, 1 = severe, 2 = risk]
annotations - TEXT
timestamp - TIMESTAMP

This way, if you make a SELECT * FROM tabla WHERE id_pac = 6 ORDER BY timestamp ASC; you will get all the patient's events; add a LIMIT 1 you can get the last record or "current status" of the patient.

If these are data that are NOT going to change and will not be used for statistics or calculation, I recommend you use a different structure using JSONB:

id - SERIAL
details - JSONB - {id: 6, detail: "detail text", other_locity: "New insanity", hour_of_income: "7:00"}
timestamp - TIMESTAMP

The advantage with JSONB is that if tomorrow you feel like changing the structure of the JSON to add or remove "fields" it can be done without any problem. The queries that you run on JSONB can also include or ignore the records that DO NOT have the new field, etc ...