Open jichengzhi opened 2 months ago
Proposed ERD from @jon65 and @shiki65536
filaments/history/1
will get a filament status history record whose id is 1, but you can't do that if you use status_id
and timestamp
as PK.opened_by
a FK to the user table?assigned_to
and opened_by
in the filament table?
opened_by
is same as filament_status_history::action = 'open'
job_filament
table to record filament usage of the job, and add a job_id
to refer to the printing jobThank you for your guidance and explanation.
About point 1.
The reason that we discussed about timestamp
as PK was some teams may prefer using less keys.
timestamp
is usually unique enough but not clean so we came out with status_id
.
We were not sure which one our team prefer so glad to know the feedback.
About poinrt 2, 3, 4 Roger 2, 4, agree with 3.
Another issue We also discussed should PrintJob and Filament be a multiple to multiple relation. I think PrintJob can reach the filament info through printer so it might not necessary, but we are not sure.
Thank you for your guidance and explanation.
- About point 1. The reason that we discussed about
timestamp
as PK was some teams may prefer using less keys.timestamp
is usually unique enough but not clean so we came out withstatus_id
. We were not sure which one our team prefer so glad to know the feedback.- About poinrt 2, 3, 4 Roger 2, 4, agree with 3.
- Another issue We also discussed should PrintJob and Filament be a multiple to multiple relation. I think PrintJob can reach the filament info through printer so it might not necessary, but we are not sure.
A timestamp is not unique in distributed system. If I have 2 printing servers (which I will do due to limited performance of PIs), they may insert data at the same time.
If a printing job may involve one or more filaments, the relationship is many to many:
Thanks @jichengzhi for uploading the design and sorry for my inactivity lately. Will be more responsive from now on.
Based on your comments I have updated this design,
Could you explain what do you mean by saturated keys? Do you mean to use a single surrogate (self defined) key like filament_history_id?
From my understanding filament_status_history has a M-M relationship with filament (represent when a user load/unload a filament) but opened_by and assigned_to has a 1-1 relationship with filament. I created a seperate filament_responsibility table to solve this.
Noted, I created job_filament below
- use saturated key if PK of your table is composed by multiple columns. This is good for REST APIs, for example
filaments/history/1
will get a filament status history record whose id is 1, but you can't do that if you usestatus_id
andtimestamp
as PK.- in the filament table, is
opened_by
a FK to the user table?is it appropriate to record
assigned_to
andopened_by
in the filament table?
- in my opinion, a filament row represents a filament pack. The usage history should be recored in the history table
- also, I think
opened_by
is same asfilament_status_history::action = 'open'
- please do not modify the existing printing job table because it ONLY records info about the printing job status. Add a
job_filament
table to record filament usage of the job, and add ajob_id
to refer to the printing job
for FILAMENT_STATUS_HISTORY table, do you think it is better to do this design? Might be more intuitive for users to track because load/unload is combined into 1 record.
Thanks @jichengzhi for uploading the design and sorry for my inactivity lately. Will be more responsive from now on.
Based on your comments I have updated this design,
- Could you explain what do you mean by saturated keys? Do you mean to use a single surrogate (self defined) key like filament_history_id?
- From my understanding filament_status_history has a M-M relationship with filament (represent when a user load/unload a filament) but opened_by and assigned_to has a 1-1 relationship with filament. I created a seperate filament_responsibility table to solve this.
- Noted, I created job_filament below
![]()
- use saturated key if PK of your table is composed by multiple columns. This is good for REST APIs, for example
filaments/history/1
will get a filament status history record whose id is 1, but you can't do that if you usestatus_id
andtimestamp
as PK.- in the filament table, is
opened_by
a FK to the user table?is it appropriate to record
assigned_to
andopened_by
in the filament table?
- in my opinion, a filament row represents a filament pack. The usage history should be recored in the history table
- also, I think
opened_by
is same asfilament_status_history::action = 'open'
- please do not modify the existing printing job table because it ONLY records info about the printing job status. Add a
job_filament
table to record filament usage of the job, and add ajob_id
to refer to the printing job
filament_loading_history
instead of status_history
since opening and assignment are also status changes.filament_responsibility
is redundant because it is duplicating data from the filament table. The previous design is good for mecorrected design:
The lab manager wants to track filament usage, currently everything is logged manually, for example: