New User with Many to Many problems

[scotty476]

Hello I am a brand new user so bare with me.

I am creating a database for stock research and every one of my relationships is many to many and I am having trouble getting my head around the relationship concept. If any of you could help out i would really appreciate it.

Here is a simplistic version of what i have:

Stock Research A:

Ticker

Rating 1

Rating 2

Rating 3

Stock Research B:

Ticker

Rating 1

Rating 2

Rating 3

Client Account:

Ticker

Name

Account

Value

I am linking the tickers from the two different rating services to the ticker in the client account but they are many to many relationships. I am not sure what kind or linking table i need to set up.

Thank you for your time.

Edited June 16, 2009 by Guest

[jamesducker]

So "Research A" is a stream of data from one source, and "Research B" is a stream of data from another source?

If so, then you have two tables essentially containing the same thing so I would be inclined to merge tables A and B in to one table called "Stock Research". You'd then need a fifth field called 'source' so that you could identify which source the data came from. With that field in place you could later on perform filtering actions so that you only take account of data from source A or source B or both. This approach also means that in future you can add sources C onwards.

I realise that doesn't answer your question but if I have interpreted the situation correctly it will definitely improve matters!

How does the client account need to link to the research?

James

[lanceomni]

Here is a basic example of a Join table. Once you understand this I can explain a bit on how to automatically create these join records.

The relationship between [Client] & [Join Table] allows for both Creation and Deletion of records in the [Join Table]

The relationship between [stock] & [Join Table] allows for the Deletion of records in the [Join Table] when a [stock] is deleted

Join_Example.zip