User Name Password Register
DaniWeb IT Discussion Community
All
What is DaniWeb IT Discussion Community?
You're currently browsing the Database Design section within the Web Development category of DaniWeb, a massive community of 402,507 software developers, web developers, Internet marketers, and tech gurus who are all enthusiastic about making contacts, networking, and learning from each other. In fact, there are 2,844 IT professionals currently interacting right now! Registration is free, only takes a minute and lets you enjoy all of the interactive features of the site.
Please support our Database Design advertiser: Programming Forums
Views: 770 | Replies: 1
Reply
Join Date: Dec 2007
Posts: 2
Reputation: sam10 is an unknown quantity at this point 
Rep Power: 0
Solved Threads: 0
sam10 sam10 is offline Offline
Newbie Poster

Question Database Design Question

  #1  
Dec 9th, 2007
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
AddThis Social Bookmark Button
Reply With Quote  
Join Date: Sep 2007
Location: North Bay Ontario
Posts: 174
Reputation: trudge is an unknown quantity at this point 
Rep Power: 1
Solved Threads: 20
trudge trudge is offline Offline
Junior Poster

Re: Database Design Question

  #2  
Jan 11th, 2008
It is never a good idea to store derived data. If one of the values involved in deriving the value changes, but you don't update the derived field, your database is now out of sync.
Last edited by trudge : Jan 11th, 2008 at 5:58 am.
Amer Neely - Web Mechanic
"Others make web sites. We make web sites work!"
Reply With Quote  
Reply

Only community members can participate in forum threads. You must register or log in to contribute.

DaniWeb Database Design Marketplace
Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)

 

Thread Tools Display Modes

Similar Threads
Other Threads in the Database Design Forum

All times are GMT -4. The time now is 5:58 pm.
Forum system based on vBulletin Copyright ©2000 - 2008, Jelsoft Enterprises Ltd.
©2003 - 2008 DaniWeb® LLC