Parent? Child? Distant Relative? Casual Aquaintance?

Hi all, I have a question that relates to a relationship in my stock performance database. It is a conceptual one, but is the only aspect of my database design that I am "losing sleep over" *LOL* (as if that could happen with me... ;) )

Anyway, Here are my tables/Keys, as setup

1) CurrentList - Primary Key is PortfolioID, StockID, BuyDate, Selldate.
- essentially, this table defines stock portfolios. A PortfolioID has one-to-many StockIDs (stocks in the portfolio), and each PortfolioID/StockID pair can further be grouped by BuyDate and Selldate (because a single stock may come in and out of a single portfolio over time).

2) StockProperty - Primary Key is StockID, CreateDate. This table is built on a daily basis from rows in the Currentlist. It represents the affected stocks currently in ANY portfolio. For example, the IBM stock may exist in more than one PORTFOLIO (PortfolioID) but there will still only be ONE row for IBM on a particular date (CreateDate) in the StockProperty table (since the stock is the same stock, regardless of how many portfolios it is in).

I am trying to figure out which is the parent table, and which the child...or if there IS an "enforceable" relationship at all!?!? None of the non-key data is the same in either table, so the only columns with a relationship is StockID, and StockProperty.Createdate - which must be between CurrentList.BuyDate and CurrentList.SellDate.

I guess that's my quandry (or one of myriad quandries in my life ;) ) - I can't really think of how to enforce (via foreign keys, etc) the relationship in the date range...AND...how to explain the relationship between the two tables (I lean toward CurrentList being Parent to StockProperty, but can't come to grips with what the probably obvious "standard" relationship description would be.

Any (preintable) thoughts?
Thanks!This is known as a many-to-many relationship, and is pretty common. An intermediary table (CurrentList, in your case) contains the primary keys of two unrelated parent tables (PortfolioID and StockID). In your example, your current list will have additonal composite key fields (BuyDate and SellDate) that describe a historical record of the many-to-many relationship.

That said, I suspect that you are going to run into more problems with your design. This is a very complicated business process to model, and you will likely run into many more many-to-many relationships and even more obscure structures before your get something robust and workable. I strongly recommend that you scale back the scope of your project if possible, and then add features as you verify your design through use.|||Okee-dokee, thanks for burning a few brain cells on this...

It's actually working in production, and as you probably guessed, this is just about 1/5th of the project's database, and the overall production database is working with no apparent design issues so far. I THINK I'm ok in that respect. As with all things, time and unforseen keystroke sequences will tell.

There are really only TWO tables involved at this part of the design though...the CurrentList and the StockProperty tables. A Currentlist row, however, can only be associated with ONE stockproperty row (in my example above, if the same stock is in the same currentlist multiple times, the BuyDate and SellDate in the CurrentList row will be different, resulting in a new and separate CurrentList row). (or were you saying that I SHOULD have the intermediary table?) Conversely, a StockProperty row can be associated with one-to-many CurrentList rows.

Anyway, I know it works, I'm just trying to step back and look at this segment of the design and try to figure out if it's designed the way it SHOULD be. Any time I see something like this that gives me trouble mapping out relationships onto paper - just is a design "uh-oh" red flag to me. (hey, Daddy, what's "paper"?)

I'm thinking that it really would be enough at one level to add a foreign key on StockID using CurrentList as the child, and StockProperty as the parent. That seems too simple though, and disregards the stockproperty.date to currentlist.daterange relationship.

Hmmm...I appreciate any insight anyone can provide...but no big rush or urgency. It's more to me like a "OK, it's out there, NOW what could I have done differently?" thing. Yeah, I know...NOT how to develop ideally, but The Machine coerced me...and now I just wanna make SURE, in retrospect, that my kid won't see this someday and think "Good God, Dad...WTF were you THINKING?" (she does that enough NOW ;) ).