Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Sean_B
Posting Yak Master
111 Posts |
 Posted - 2006-12-12 : 12:02:35
|
| Hi,I've a question about how to implement a scenario, and would appreciate any suggestions anyone cares to offer. I'm creating the following tablesFutureContractEquityThese (and others), may have an option placed on them, I was planning to create seperate option tables for eachi.e.FutureContractOptionEquityOptionWith a FK pointing at the PK of the tables above.However the data held in the option table looks the same for both, and I'm considering trying to use the same Option table for both the FutureContract & Equity tables.There is a problem here with the Foreign key referencing the Primary key of the parent table.I could add two foreign key columns and allow them to be null, so that only one is entered, however we can have an option on a lot of different things, so I might end up with a great list of these columns.Table - Option FK - FutureContractPK FK - EquityPKI'm using an unique number (identifier) column for the primary key of all tables.I'm sure this is a relatively common problem, and would be grateful for any suggestions about how I might approach this.Sean |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2006-12-12 : 13:09:45
|
| I'm probably misunderstanding the problem but can't you put the FK constraints on your two tables: FutureContractOption and EquityOption?ie:create table FutureContractOption (optionid int references option_tbl (optionid) )create table EquityOption (optionid int references option_tbl (optionid) )Be One with the OptimizerTG |
 |
|
|
Sean_B
Posting Yak Master
111 Posts |
Posted - 2006-12-14 : 09:18:06
|
| Hi,I've gone for the approach of having a single option table, to this end I had two approaches that I could seeFirst ApproachThe Option table had a foreign key to either the FutureContract table or the Equity table, this meant that only one foreign key was ever populated, the downside of this approach was that I had to add a new foreign key to the Option table every time I added a new Asset that could have an option such as a Forward.Second ApproachI decided to go for intermediary tables between each of the asset tables such as FutureContract and Option, so that the option table doesn't have any foreign keys, this means that I don't have to add a new foreign key for each new type of asset.I now have the table FutureContact which can be joined to the Option table via the FutureContractOption table which contains only the keys of the option and FutureContact.I think this approach will work, I just wanted some feedback from anyone who'd worked on anything similar. Sean |
|
|
|
|
|
|
|
|
Multiple Parent Table Question