Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  General SQL Server Forums  Database Design and Application Architecture  Help With Database Design!!

Author  Topic  bjashwin Starting Member 1 Post Posted - 2011-06-30 : 13:44:27 Hello, I am being asked to re-design/improve a DB table designed by a Developer which will hold the errors/Messages for different applications. Now in our Data Base we have a [Message] table with the following columns [message_key] int (PK) PrimaryKey [name] varchar(200) Name to identify the message. [type] varchar(30) Error,Warning [short_message] varchar(500) Short Message detail [long_message] varchar(500) Message details [number] int same as the message_key This holds all the generic warning and Errors for 2 applications. Now Currently in one of our applications we have the following tables Order table and Order Details table. Now the application ( win form in this case ) needs to process all the orders for a particular person. When processing the order, if there are errors on the order detials it would be displayed to the user. Now these errror messages per order detail will be stored in this generic message table which my Lead developer came up with called the Message_Set table. Message_SET table message_set_key int Key that identifies a set of entries message_entry_key int Identity field that uniquely identifies an entry type varchar(30) Message type message varchar(1000) Message string number int Reference to [number] in [message] table. May be NULL Message_Set_Key willl be Key which will be unique for a set of entries. For example in our case Suppose the order had 5 order details and when processing the order detail 3 order details errored out, and those erros were not found in the current [Message] table then the Message_Set table will have 3 entries liek this 123456 1 Error "Product not in stock" Null 123456 2 Error "Product not in stock" Null 123456 3 Error "Product not in stock" Null If you notice the message_set_key will be the same, since its for the same order, but the message_entry_key will be differnt which will be an idenity field. The order_detail table will have a foreign key to this Messsage_Set_Key in the message table. NOTE: The number in the above 3 entries is NULL since the error does not exist int he [Message] table. Now suppose if we have an errror on the [Order] itself, the entry in Message_set table would be 123457 4 Error "Credit Card Not Valid" NULL And the order table would have a reference to the Message_Set_Key table. So this way any table can reference this Message_set table by just having a foreign key Message_Set_Key to the Message_set table. Now I wanted to know a couple of things. How can I make the Message_Set_Key unique for differnt messages accross differnt applications. Since the message set key will be the same in cases where there are messages for order_details. Can i use a GUID ? if Yes do i need to check in the application if the generated GUID is not being used in the DB before using it ? & Is there a better way to design this generic Message_set table ? P.S: Wanted to let you guys know that we also have an Application table which lisits all the application details dbo].[application]( [application_key] [tinyint] NOT NULL, [name] [varchar](100) NOT NULL, [description] [varchar](400) NOT NULL) Hoping for some help. Thanks, Ashwin