I'm not reporting this as a bug, more a question as to whether there's a specific reason it's done this way. I guess it's a house style but I notice in the new 4.20 beta that for example, Category.ParentCategoryId and Category.PictureId have zero as the 'nothing' value rather than having null (and a nullable field).
Same with Product.DownloadId, Product.GiftCardTypeId and others, I can't find a single nullable FK from a wander through so I guess it's across the board.
I've always used a nullable FK and used null for 'not a sausage' rather than zero and always found it done that way that in numerous existing client databases when contracting in the UK so it seems like very much the standard practice.
It's the same in my circa NOP3.1 database too, so not a recent change with a view to being compatible with some DB I've not come across that doesn't do nulls... if there is such a thing, so I thought I'd ask.
Is there a specific reason to do this ? - I ask partly out of curiosity and partly as there may be a good reason to do it that way that I've been missing all these years.
*added soon after*
It's late night here, so I'm being a bit slow but I just realised that this means there's no enforcing of referential integrity, there being no Category zero... I told a new product that it was associated with vendor 123456 and the database was happy with that... err, isn't this a terrible idea ? - apologies, not meaning to be a die-hard fuddy-duddy but isn't referential integrity fairly important ?
Use of zero, rather than null foreign key values
- 39
- 225
- 2/17/2013
- Indonesia
I just had a good search around to see if the industry had decided RI wasn't all that important after all and am pleased to see that it hasn't.
When I was first starting in IT, a clever lady called Susan sat me down after looking at something I was working on and asked how I was sure I had no orphaned records or that this key actually led to something in the associated table, I told her 'Oh, my code does that', she smiled politely and gave me a book on database design to read.
Is the NOP schema like this for performance reasons or to make testing easier or ? - again, not meaning to ruffle any feathers, am happy to be told why it has to be that way in this case but it's rather blown my mind that it is.
I'm now wondering how long it'll take me to make it not so, which of course would scupper any possibility of staying with the upgrade path from 4.20. Obviously knowing why it's done this way makes a difference to whether I can do that or not anyway.
When I was first starting in IT, a clever lady called Susan sat me down after looking at something I was working on and asked how I was sure I had no orphaned records or that this key actually led to something in the associated table, I told her 'Oh, my code does that', she smiled politely and gave me a book on database design to read.
Is the NOP schema like this for performance reasons or to make testing easier or ? - again, not meaning to ruffle any feathers, am happy to be told why it has to be that way in this case but it's rather blown my mind that it is.
I'm now wondering how long it'll take me to make it not so, which of course would scupper any possibility of staying with the upgrade path from 4.20. Obviously knowing why it's done this way makes a difference to whether I can do that or not anyway.
- 39
- 225
- 2/17/2013
- Indonesia
Ok, so I found https://www.nopcommerce.com/boards/t/36706/why-are-there-no-foreign-keys-in-sql-server-tables.aspx
..and read the referenced articles etc. If NOP was some intensive batch processing system with huge amounts of updates (that's mainly the sort of thing I implement) then that's one thing but it only updates records at the speed that humans can type (even with lots of them) so I don't think it can be justified by performance, and I can't find any other good reason.
Anyway, I just wanted to mention I'd seen the thread above now, clearly there are FKs, lots of them; the point is that they're not enforced. I still have the same questions, if anyone can answer I'd much appreciate it, thanks.
..and read the referenced articles etc. If NOP was some intensive batch processing system with huge amounts of updates (that's mainly the sort of thing I implement) then that's one thing but it only updates records at the speed that humans can type (even with lots of them) so I don't think it can be justified by performance, and I can't find any other good reason.
Anyway, I just wanted to mention I'd seen the thread above now, clearly there are FKs, lots of them; the point is that they're not enforced. I still have the same questions, if anyone can answer I'd much appreciate it, thanks.