(assignment->document.attribute(s) + assignment.date) -> pricelist.price
Creating relations has the integrity advantages: it is not possible to create an assignment, which price is not defined in the pricelist; precludes the pricelist entry removal if it is referred by any assignments.
Should a view, which combines all the foreign fields into one virtual table, be created to make establishing the reference possible?
Not quite sure I understand the entire request. However, it seems you should be able to enforce referential integrity via Instead Of trigger.
Perhaps, you could give us some sample DDL and desired output to better describe your issue. We might be able to help further then.
|||Normally, you have all the tables interrelated. The reference (a foreign key) points to an object in another table specifying the "container" it belongs to. For instance, many books refer a single author.Sometimes, you need to establish a complex reference consisting of multiple fields. For instance, a job refers to pricelist. The options in the print job specify a "service id", which has a unique price in the pricelist.
Suppose now that the pricelist can be updated. When job is created, it fixes the latest service cost in int field, the pricelist date. So the cost is uniquely identified by the job options (some fields) and the date. This is a complex key.
What I have faced is that nobody addresses the possibility of having the job attributes fixed in a separate table (say, documents to be processed always have the same settings). A job refers a document, from which the attributes are derived and coupled with the pricelist date identifies the job cost in the pricelist table. Effectively, the complex key is composed from fields located in different tables. A record contains only a part of complex key plus a reference to another entity, which has a rest of the key.
One way to create a relation would be to produce a view joining the key field tables. However, views are not enabled in diagrams. I suppose the reason is because the views are not allowed to participate in data relations.
Actually, I have decided that in my case I do not need to fix the job settings in the referred document, so I'll have all the key fields in one table. Yet, the topic is quite general to be interesting for me and others.
