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 |
|
Blastrix
Posting Yak Master
208 Posts |
 Posted - 2004-12-02 : 16:36:18
|
| I'm in a situation where I need to record all requests that come into the system, even if they do not pass validation. varchar fields aren't an issue but anything which is a FK poses a problem. As an example, if a value for CountryCode is received as A2, I still need to save this information. Since there is no CountryCode out there that is A2, I now either have to not use relationships for this data, or I have to come up with another solution. I really don't want to have to use another table. I'm wondering what any of you have done or would do in this situation? One of my thoughts was to make two fields for each FK in the table. One that would hold legitimate values, and another varchar based field that would store invalid values. If the value in the legit field is a known invalid identifier, then I could just display the bad value in the presentation.Any thoughts?Thanks,Steve |
|
|
Wanderer
Master Smack Fu Yak Hacker
1168 Posts |
Posted - 2004-12-03 : 07:03:58
|
| Assumming you cannot change bthe application to use the reference data table to populate some kind of drop down, for example, to enforce a valid value, and you cannot correct the process to ensure that you get valid data, and you cannot go the route of having a staging type table to stage incomplete data, your 2 column process would work, I guess.Extending you "2 column" FK idea ...How about making your xxx_FK column nullable, and inserting nulls for "invalid" FK's, and the incorrect value into the xxx_BAD_FK column, then when doing presentation, use "ISNULL(xxx_FK,xxx_BAD_FK)"*##* *##* *##* *##* Chaos, Disorder and Panic ... my work is done here! |
 |
|
|
Bustaz Kool
Master Smack Fu Yak Hacker
1834 Posts |
Posted - 2004-12-05 : 08:24:29
|
| IMO, the two column approach is a bad idea. A table should hold one type of entity. Your design implies that the table will now hold 1) Processed Data and 2) Rejected Requests.HTH=========================================Let X = {All sets s such that s is not an element of s}(X element of X) ==> (X not element of X)(X not element of X) ==> (X element of X) (Bertrand Russell Paradox) |
|
|
|
arpp
Yak Posting Veteran
61 Posts |
Posted - 2004-12-05 : 12:00:30
|
| Insert a dummy row in the Foreign key table with identity 0.(you may have to turn the seed off and on again).But this way you would still have the relation and any time you come across invalid data app would populate 0 as FK value. I personally do not like the idea of having dummy rows but this could be an alternative. |
|
|
|
|
|
|
|
|
Storing invalid FK breaking data