Synthetic keys and circular references at relations

[Anonymous]

Hi,

I have a problem creating my data structure. The problem is that I have to display relationships between different documents with different unique IDs, but at the same time I have to offer the possibility to select them by date.

(It should be possible to see all children of several fathers and to restrict them by date.)

In my attempts I had either synthetic keys or circular references.

Is there a way to avoid them? In the attachment I have an simplified example.

Thanks in advance.

[Lisa_P]

Hi Julian,

I'm not sure why you need such complex relationships.

Is it possible to have one key that follows from Inquiry to Offer to Order ?

If so, you could then concatenate the data into one table and add one more field based on the Stage or Type like this ..

ID     Type     Date

001     Inquiry     05.07.2018

001     Offer     15.08.2018

001     Order     15.09.2018

Removing the need for so many tables.

[Anonymous]

Hi Lisa,

thanks for your reply.

Unfortunately, I can't simplify the whole thing to one key.

If, for example, I have an inquiry from which five offers arise and each offer again has two orders, can I no longer comprehend that? In addition, it becomes even more complicated if I only want to see some offers of the five in a certain period.

If this is possible with your suggestion, can you please create an example for me?

Thanks.

[marcus_sommer]

I think you need rather an approach like this one: Canonical Date.

- Marcus