All:
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_NO
……other_details
MAG_IN_SERVER ( a child table to store all messages coming in)
MAG_NO
SERVER_ID
MESSAGE_TYPE
MAG_STATUS_CODE
DATE_IN
DATE_OUT
DIRECTORY
LOCK_STATUS
DATE_LOCKED
COMMENTS
CREATED_DATE
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)
SERVER2_IN_YN
SERVER3_IN_YN
SERVER4_IN_YN
Locked_YN
Assigned_TO
2. I can add another MAG_STATUS table to store the status records like this
MAG_STATUS
-----------
MAGZ_NO
STATUS_TYPE
STATUS_CODE
1000, TEST1, P
1000, TEST2, F
1000, SERVER1_IN, Y
1000,SERVER2_IN, N
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.
MAGZ_STATUS
-----------
MAG_NO
STATUS_CODE
Are all design normalized and valid. Any design is better than the other. Any new proposals