I have an application where users are tracking a workflow around producing a derivative work from an existing Book. The derivative work, we'll call "BookDerivative" must have a book that it relates to from a data model perspective. The existing system which stores the Book info (Title, Author, etc) must be considered an "optional third party" system that can be interchanged with another system in the future (which I've abstracted via IBookService).
My application must provide two options for users when they create a BookDerivative:
- Select an existing book from the IBookService provider (via lookup); OR
- Create a "free form" entry for the Book which would NOT be linked to the IBookService provider. This allows the user to create a BookDerivative for a Book title not found by the IBookService lookup.
My solution to support this is to include the needed columns from Book on my BookDerivative table/entity (BookTitle, BookAuthor, BookId) where BookId will only be present IF the book comes from the third party system. This way if the book is "free form" I would only populate BookTitle and BookAuthor.
Future accesses of BookDerivative do not need to look to the IBookService. The essential book info should never (or rarely) change in the third party system and I'm prepared to except the fact that it is theoretically possible for information to be out of sync.
There will be no data-integrity between the two systems and no FK. My application cannot assume the underlying data model of the Book system but instead must rely on a defined model and IBookService lookup.
My question remains, is this a good way to solve this problem? What recommendations or pitfalls might exist? I would think this is a common requirement, have you used a different or better solution in the past?
Best Answer
Based on the details you've provided I'd say it sounds fine.
Things that may or may not be a problem based on further information.
(1) Even though you say there is no relationship between the two systems and no foreign key, if you were to switch third part systems, you would be left with a bunch of meaningless ids for books that were added from the previous 3rd part system. Is that okay? If not, you may need to add a lookup table of third party systems that can related to the IDs in the Book table so you can later tell which system they came from.
(2) If the only reason for the IDs from the third party system to be there is to check for their existence, maybe a simply IsFrom3rdParty or IsFreeForm boolean column would be sufficient. It would seem to meet all the requirements you lay out here and would be simpler than the ID to implement and would better convey the actual information you have (which is simply that it came from a 3rd party system. You can't guarantee that the ID will relate to anything especially if the backend system has changed)
Otherwise, yes it sounds good.