I have a question on how best to store a deived values for status for a system.
I want to pick up some thoughts on alternatives/better to design a DB for this.
1. I have an “E_Magazine” that gets reviewed by several sections.
2. During the process, the magazine is flagged to be moved from one server to another. There are 5 servers involved. The magazine can exist on several servers at same time.
3. After a magazine is marked to be moved, a batch program moves the book from one machine to another machine. I need to track the actual move when it is done and the location of the book.
There are many batch programs that run on servers and they all send status messages to the DB via HTTP HTML forms.
I create a second table to store all the messages received for a book. I have four types of messages:
Move result – message to indicate if book moved in/out of a server
Test result – message to indicate (P/F) if test passed or failed. There are two test on server 1 done. They can be done many times. The test message can also include lock status of a magazine.
Lock result – a message that tells if lock status changed for a magazine on the first server.
Assignment results – a message that tells if a magazine was assigned to a reviewer.
MAGAZINE (This is master table)
MAG_IN_SERVER ( a child table to store all messages coming in)
I can always get the final status of a message type by checking the last record for that message type.
However, I want to store that derived value every time I add a status message so I can see the last/current status of a book.
I have two ideas:
1. I can add columns to master MAGS table like this
SERVER1_IN_YN ("Y" if it exists and “N” if it does not)
2. I can add another MAG_STATUS table to store the status records like this
1000, TEST1, P
1000, TEST2, F
1000, SERVER1_IN, Y
1000, LOCKED, Y
1000, ASSIGNED_TO, JOHN
Or I have one table with one status code, but I have to build too many combinations of statuses. (i.e, a magazine can exist on server 1 but not on 2 and 3 but exist on 4) etc.
Are all design normalized and valid. Any design is better than the other. Any new proposals