I am having a problem when modeling a Foreign Key in an "Operations" table. This table holds all information on customers ′s applications and withdrawals.
Here is the structure:
CustomerID int, SourceID int, Value decimal (16,2), OperationDate datetime
Well the problem is that SourceID sometimes might be NULL depending on how the record was inserted. So its kind of cumbersome to define it as an FK, since it can be null...To get things worse, this SourceID might point to more than 1 table (depending on the CustomerType it will point to SourceA table or SourceB table)...
How should this be modeled?
What is sourceId? It sounds like you need to do a bit more normalization here and have a source table.
source
sourceId int
customerType
<other source bits>
Then your other tables like SourceA and SourceB reference this table, as well as the operations table:
create table operations
(
...I assume you have a key other than these columns,
customerId int
sourceId int null references source(sourceId)
)
Then the source will either be sourceA or sourceB depending on the customerType (assuming you are defining the source for a given customerType.) I am not sure that I am making sense, but this sounds like what I am getting from your post. If not, can you give your current table structures and a bit more about usage?
