Avoid synthetic key, but the relations needed

[shirleyc40]

I have 3 tables, one Event, one Quote and one that's a master date (which are all the distinct dates from the event and quote table because I wanted to have charts on the same date field). The event and quote table have to be connected by ID number but then the Event and Quote table connect to the master date table using the same field as well so it creates a synthetic key of date and id. 

How can I work around this?

[dplr-rn]

Synthetic key by itself is OK as far as performance is concernced but its an indication that there maybe data issue.

question/suggestion to you
1- Does the quote table need EEL date? i.e. are there multiple EELDates for same quote number? 

if not your QuoteInfo table really does not need EELDate field. just delete or rename the  column in QuoteInfo table

2- If YES create a composite key in both tables with EEL Date and QuoteNum and delete columns from one of the table

[shirleyc40]

The same quote number doesn't have multiple EEL Dates but when I try to remove it, it renders some of the quote numbers as null date. If I create a composite key, can I still create charts with the date as a dimension?

[dplr-rn]

i would say look at your data more closely. If some quote numbers are coming as null data

that may mean EELDate is more reliable on Quote table. delete it from other table.

I am assuming from your response Quote table always has date and other table may not. is there scenarios where you have an event with no quotes?

you need to understand your data more clearly. biggest part of modeling is an understandign of data

[QFabian]

Hi, try creating a compound field in both tables, like this :

EELDate & ' | '&QuoteNum as  %_Key_DateQuote,

and you have to left the separated fields in one of the tables ofcourse.

[shirleyc40]

But then how could I use EELDate as a date dimension on charts