Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716

    Unanswered: Foreign Key Quandry...

    Hi y'all!

    I have a table (Portfolio) with the following columns:
    1)PortfolioID
    2)StockID
    3)Date

    Interestingly enough, I also have a second table (stock) with the following columns:
    1) StockID
    2) Date

    The relationship I am trying to enforce is one in which the Portfolio table is made up of many stocks, and the Stock table rows can be components of many Portfolios. Many-To-Many in that respect (OMG! Am I enforcing an orgy-type relationship? )

    My Portfolio PK is all three columns. My Stock PK is both columns. Obviously both tables contain other data, but that isn't necessary to figure stuff out here, I don't think.

    My portfolio table defines portfolios that consist of multiple stocks. However, the Stock table will never have a duplicate stockID on any given date. The reasoning here is that a stock's data (the "other stuff" in the stock table) won't change from portfolio to portfolio, since the smallest unit is a stock, and that stock's performance data for a given date will be the same regardless of how many portfolios it may be a part of.

    On the other hand, a portfolio MAY consist of multiple instances of the SAME or different stocks.

    Many-To-Many

    Attempting to add a FK constraint where the primary key table is STOCK, and the FK table is PORTFOLIO was my way of attempting to enforce at the DB level that there is only ONE of a given stock (defined as a distinct StockID and Date pair) for each day, but it can be associated with one-to-many PORTFOLIOs. The FK is StockID and Date.

    It all seemed so simple... but of course, since I create my portfolio before the STOCK rows associated with it (stock rows are only created as needed, based on the portfolio rows that define the stock list that makes up the portfolio), the FK constraint fails (no primary key row exists when the FK row is created).

    It bothers me to remove the constraint, even though I know I must...

    How would one go about enforcing this type of relationship? What I really want is the constraint to reverse the PK and FK tables...but the STOCK table's columns are a subset of the PORTFOLIO table's primary key...so I can't do it that way (error due to PORTFOLIO's columns not being THE PK or having a unique constraint (which they cannot have, since the same StockID/Date can exist in more than one PORTFOLIO.

    Any thoughts from anyone able to stay awake and interested by this time?

    Thankspaul
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  2. #2
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    I would think you would want to do this:

    Portfolio
    =======
    PortfolioID
    Information about a particular portfolio.

    Stock
    ====
    StockID
    Information about a particular stock

    PortfolioStock
    ==========
    PortfolioID
    StockID
    Date
    Active --Or something to differentiate what a given portfolio contains. You also might want version or something.
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

  3. #3
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    Thanks Derrick,

    As is typical, the "Big Guy Who Signs My Paycheck" has reassigned my priorities, but I've made a note of your suggestion for when I get back on this project again (it always seems to be lurking out there).

    I'm a bit confused by your suggestion, as at first blush it seems like adding another layer of abstraction that I'm not sure at this point that I need. My model is essentially what you have posted, with the exception of the Portfolio table. In your post, my Portfolio table = your PortfolioStock table, and my Stock Table = your Stock Table.

    So I'm not sure why I wouldn't run into the same issue in your suggested model. ???

    The BIG GUY and I were discussing it some, and had a difference of opinion in the chicken-or-egg scenario, meaning in his opinion the STOCK table is the parent, and the PORTFOLIO table is the child.

    It makes sense in a "that's stretchin' it, Boss" kinda way...but I need to rethink when I get back on the project (or on the sideline) so I can argue better when the time comes.

    Thanks again for your reply!
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •