Database Design Q

[GrimStoner]

Hi

I've never used relationships at database level (FK's). Most of the time I've handled all relationships in the application logic level of my apps. We started using a new ORM tool, which requires relationships to be declared in the db. Which should have been a simple thing is now a headache...

In the simplest form, I have two tables : Events, and EventLog.

EventLog contains multiple entries of a single item in Events (i.e. if Event was a car accident, EventLog would contain "Accident happened", "Ambulance arrived", "Paramedics helps victims" etc.).

The crux of the tables are :

Events
------
EventId - int, PK

EventLog
---------
EventLogId - int, PK
EventId - int

However, when I try to create a FK between EventLog.EventId and Events.EventId fields (btw, using SQL Server 2008), I get this error :

The columns in table 'EventLog' do not match an existing primary key or UNIQUE constraint.

So I've read that FK's can only be matched to PK's on the other table... Does this mean that I have to have a linking table in between these two tables? Something like...

EventLogsToEvents
-------------------
EventLogToEventId - int, PK
EventLogId - int
EventId - int

... and then have the Events and EventLog table link to this table with FK's?

[RuadRauFlessa]

No need for the table in between you will only do that to resolve a many to many relationship
What you need to do is create the FK on the other table.

[GrimStoner]

Um... *cough*... well that settles that.



thanks

[RuadRauFlessa]

So I take that as it is working

[GrimStoner]

Yes it does.

[RuadRauFlessa]

Cool