1

I've never come across this situation, not sure how to go about it.

Let's say I have a table Portfolio. I also have two tables: Asset 1, and Asset 2. I need to create a bridge between Portfolio and the Asset tables. A portfolio can have either Asset 1, or Asset 2, or both, or none.

enter image description here

Can I do something like the bridge table in the image above, i.e. where the bridge table (example below) would contain in the same column, the foreign keys to two different tables

enter image description here

0
-- Asset AST of asset type ATYP exists.
--
asset {AST, ATYP}
   PK {AST}
   SK {AST, ATYP}

CHECK ( ATYP in ('A', 'B') )
-- Asset AST, of asset type "A" exists.
--
asset_a {AST, ATYP}
     PK {AST}

FK {AST, ATYP} REFERENCES asset {AST, ATYP}

CHECK (ATYP = 'A')
-- Asset AST, of asset type "B" exists.
--
asset_b {AST, ATYP}
     PK {AST}

FK {AST, ATYP} REFERENCES asset {AST, ATYP}

CHECK (ATYP = 'B')
-- Portfolio PTF exists.
--
portfolio {PTF}
       PK {PTF}
-- Portfolio PTF contains asset AST.
--
portfolio_asset {PTF, AST}
             PK {PTF, AST}

FK1 {PTF} REFERENCES portfolio {PTF}
FK2 {AST} REFERENCES asset     {AST}

Note:

All attributes (columns) NOT NULL

PK = Primary Key
AK = Alternate Key   (Unique)
SK = Proper Superkey (Unique)
FK = Foreign Key
| improve this answer | |
0

If you know you will have only those two Asset tables, I would go with the option of having them both referenced from Portfolio table. Portfolio would therefore have two nullable FKs - one for Asset1 and the other for Asset2. No need for extra lookup table

| improve this answer | |
  • The actual data structure I am working with contains 12 tables, each of which can be part of a portfolio – George Well May 20 at 7:00
0

You have an N-M relationship between Portfolio and Asset1, and another between Portfolio and Asset2. Since Asset1 and Asset2 are stored in completely separate tables, we must presume that they are different things, otherwise you’d just have a table “Asset”. As such, it is not wise to try and treat them as the same thing.

If you are proposing to store your data something like so:

PortfolioID     AssetID
1               Asset1_9
1               Asset1_12
2               Asset2_123

Put mildly, this is a horrible idea. Most databases are designed such that the value found in the intersection of a column and a row is a single, discrete value. If, upon retrieving this value, you then have to use additional processing power to further split it up through some arbitrary means, and you lose most of the power of having reduced your data to rows and columns in the first place. This is often referred to as “Smart Data”—here, two facts stored in one place, where there should only be one.

Is there a strong reason not to just have two separate N-M table, one for Portfolio/Asset1, and one for Portfolio/Asset2? It is more work and effort, but it would seem to accurately represent your data.

Alternatively, you could have three columns:

PortfolioID AssetType   AssetID
1           1           9
1           1           12
2           2           123

This is similar to the “smart data” solution; you’ve got all data sorted out into its own columns, but you still have to do a lot of If/Then processing work: IF AssetType = 1 then look up AssetID in Asset1, ELSE IF AssetType = 2 then look up AssetID in Asset2, etc. Yuk.

Are the assets themselves some kind of type/subtype? Should you perhaps have tables for Asset, Asset1, and Asset2? This would end up looking something like

Asset
AssetId
AssetType
Name, Description, etc.

Asset1
AssetId  (FK to Asset)

Asset2
AssetId  (FK to Asset)

Portfolio
PortfolioID

PortfolioAsset
PortfolioId
AssetId

The above is just an outline. Type/subtype models can get complex, much depends on what you are modeling, but it may be worth considering here.


In response to the first comment:

12 vs 2 tables makes it a significantly more complex problem to address. A lot depends on how similar the myriad assets are. Can they all be treated as a large set of type/subtypes? Not just with regards to Portfolios, but for the application as a whole? This seems unlikely.

Relational purists would argue for twelve N-M tables, and that is a valid if more fussy to implement solution—weigh the cost of development vs. the cost of long term management and maintenance, noting that “tight” relational databases tend to be easier to support over the long haul (i.e. less slop to clean up over time, easier to make changes, and other less obvious benefits).

This might be a case where deliberate denormalization to improve performance might make sense—in which case I’d go with a {PortfolioId, AssetType, AssetId} structure. The big problem with that (besides some really ugly queries) is you can’t apply foreign keys to ensure relational integrity to the many asset tables, which can lead to sloppy data problems.

At this point I recommend reviewing the three possible models against your overall system requirements and likely future uses, pick one, and watch it closely to see if it works out.

| improve this answer | |
  • "Is there a strong reason not to just have two separate N-M table, one for Portfolio/Asset1, and one for Portfolio/Asset2?". In reality my data structure is made of 12 tables, so making 12 N-M tables and having to look through them to find the assets that are in a portofolio seems not ideal. The assets are types in a way. Most of their attributes are the same, apart from one of two. I.E. Asset type Cash will not have an ISIN, while having an account number, while Asset type Equity will have an isin, and no account number. They could both be part of the same portfolio, There are 12 like this – George Well May 20 at 7:00
  • It got a bit long, so I added reply to comment to my original answer. – Philip Kelley May 20 at 15:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.