How to avoid circular references

[#1]

Hello,

I have a problem setting up my data model. I have following tables.

MasterCalendar (Manually created a calendar of the past 3 years)

Items - ItemKey

Relation - RelantionKey

Orders   - OrderDate (has to link with the mastercalendar)

               - Relationkey (has to link with relation table)

              - Itemkey (has to link with item table)

Offers - Offerdate (has to link with the mastercalendar)

           - relationkey (has to link with relationtable)

           - itemkey (has to link with item)

I can't create a datamodel which has no circular reference.

Anyone has  an idea?

Thank you.

[alexandros17]

Try to left join mastercalendar to Orders and Offers, in this way the table disappears ...

then if you can left join (or join) other tables in order to destroy circular references

[#3]

Hi jens,

You could use two methods...either Concatenate or create a link table.

The simplest would be to concatenate your offers and orders tables together, You could add an addition source field to allow you to identify the two datasets.

The link table method would involve created a new table containing a concatenation of the common fields in the orders and offers table and then creating a composite key to reference back to the offers and orders tables.

Generally, I would suggest the concatenation method.

KR,

K

[er_mohit]

Using join,Left join for both  or you can use alias means change the name of field

then you can remove that circular refrence

[#5]

Hello,

Thank you for the quick reply.

The mastercalendar has several columns. Fulldate, Year, Month, Day, ...

If i leftjoin the mastercalendar to orders and offers. I join all these columns to both tables. Both if  i want a filter all orders and offers for last year? How can i do this then?

Thank you

[Gysbert_Wassenaar]

Well, one way would be to concatenate the Orders and Offers tables into one table. You could also try creating a link table. Here's a simple example. This document may also be useful to you.

[#7]

Hello all, thanks for the replies allready!.

Accualty i have 7 tables ( i said 5 because in essence it is the same i thought)  There is also a shipnote and invoice table. Which relates the same as the order and offers tables.

We were using a linktable, but this was very slow and taking lots of memory for simple tasks. So I was finding a way to remove the linktable.

Is it still usefull to join the 4 tables then, considering about 500k to 1 million records in every table?

Thankyou!