question

Tigran avatar image
Tigran asked

db design help

I'm trying to design a db for an application. Need help in determining if I'm on the right track. In a nutshell I have some information that needs to be submitted to a 3rd party the information is batched so x number of accounts make a batch and a batch is submitted. There are different kinds of batches. After the information has been submitted I get a reply from the 3rd party. The reply is pulled by the batch id. Reply has finite number of errors and status codes. So given that description what I have so far is the following. Table Batch Fields BatchID, BatchStatus, BatchType Table BatchData Fields BatchDataID, ItemSubmissionID, ItemStatus, lots of data fields Table BatchItemSubmission Fields ItemSubmissionID, ItemID, BatchID Table BatchReplyData Fields BatchReplyDataID, ItemSubmissionID, ReplyStatus, ErrorCode1 Am I on the right track? Is this a good design? How would you make it better? Thanks
schema
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Matt Whitfield avatar image
Matt Whitfield answered
You are sort of on the right track. It seems like ItemID in BatchItemSubmission points to something external that holds the data to be submitted - so have you replicated that data in BatchData? If so, is that because ItemID might change and you want to hold a reference to the exact item that was actually submitted? A stylistic thing - ItemSubmissionID would probably be more in keeping if it was called BatchItemSubmissionID - follows the pattern of the other tables then. Also - why 'ErrorCode1'? If there are multiple errors per ItemSubmission, then you should really have them in a separate table to be in 3NF (Third Normal Form). I would be tempted, personally, to put ReplyStatus and ItemStatus into BatchItemSubmission - then have a separate table BatchItemSubmissionErrors if there can be more than one error per entry, or add an ErrorCode column if not. Just some thoughts. It's a little difficult to give quantitative advice without knowing more about the system.
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Yes ItemID refers to external data. The data is replicated because it can be changed for items that are submitted. I agree BatchItemSubmissionID is better. ErrorCode1 because there is a finite numbers of errors passed back. There are specific error fields I think 5 or 6. So I didn't want to seporate them into a different table and that's not going to change anytime in the foreseeable future. You hit the nail on the head. Should I put ReplyStatus into the BatchItemSubmission table along with the error codes and remove the BatchReplyData table? The submission and reply might happen days apart you don't see a problem with having nulls sitting in that table? Thanks
0 Likes 0 ·
No - I don't see it as a problem. In fact, I see it as a benefit that you can quickly tell which items and data have been submitted but not replied to without joining 2 or 3 tables together...
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.