Nullable FK within one to many relationship
-
Hi Guys,
i noticed that my problem appeared from one to many relationship with nullable FK.
My model is:
In my model Each topology can have different Association with different tables.
So Topology 1 will have Version, ServerSizeInfo and Supported Systems but Topology 6 will have only NWLanguage.
So within t_Clients there can be nullable fields FK. (or there is the better way to build relationships here to avoid them?)To avoid nullable FK i can create a lot of one to many relationships from Topologies :
Topology-->Intersection Table --> Enitity specific for topology,
example like here:It is good practive to use nullable FK?
Why?
How to avoid them within this example?If nullabe can damage Database Integrity - how and why?
Best Wishes,
Jacek -
Why are you trying to avoid nullable foreign keys if they are valid data states?
-
Honestly?
Because i heard that nullable FK are not good for database integrity.Best,
Jacek -
Please help,
Jacek -
Hi Guys,
If i have nullable Fields and cannot have RI here it is still good aproach ?
Jacek -
It depends.
Theoretically, null able FK does not violate RI.
But you need to analyse you specific practical business case and tell which approach is correct for it.
NULL means "unknown, not present".
Is a client with not specified topology a real business case?
Make no mistake - "generic" is not "unknown".
If your answer is "yes" then nullable TopologyID would be acceptable.
_____________
Code for TallyGenerator -
Thank you Sergiy,
yes, answer is yes.But you know my ClientTables can have a lot of nullable fields, for example for Versions or Supported systems also.
It can be a problem.I should have one big table with many to 1 relationships between tables.
Between versions, supooirted systems, topologies etc.
So it can be hard to manage it.What for example if one version will change to newer? But only for one client and specific topologies?
Best,
Jacek -
What for example if one version will change to newer? But only for one client and specific topologies?
I don't understand what you mean here. First, as has been said, there is nothing inherently wrong with a nullable field that has a foreign key reference on it. That may cause an argument by those who avoid NULLs as an absolute rule. I don't, though I do look to minimize them.
The sentence above seems to be a different one, however. That the field is nullable, but only for certain clients. The column is either nullable or not. If only certain clients can actually have it be null, this will have to be enforced with business logic. As you don't provide any details about the supporting application, but business logic as a general rule is best left off the database. In a client server application its on the client side. In an n-tier it's in the middle tier. Sometimes it's best in the database. This might be one of those times. If I understand the issue correctly, I would consider a trigger, as I use those to enforce data constraints that can't be enforced with standard RI methods. I don't use these often, however. Just saying that thought comes to mind as a possibility.
-
Thank you
That the field is nullable, but only for certain clients. The column is either nullable or not.
exactly. Field can be nullable for certaqin clients.
Client 1 can have Suported systems but Client 2 here will have null because he is not using any systems 🙂Best,
Jacek -
That doesn't seem quite the same thing. I assumed you meant that for some business reason some clients can't ever be nullable. If it is simply a case of a client having systems will have an entry and others without systems will have a null in that column, then it's straightforward.
In this case, however, I would consider an entry in the parent table that said no system and was represented by a blank string or another code. Null is best used where the state is uncertain. If it's not there and known to be not there, an entry according that would probably be better.
-
Thank you RonKyle!
Parent table ? Ok sounds nice.
I know how to do it know!
Thank you!Best,
Jacek -
The parent table is the source (the one end ) of the foreign key. The child table is at the many end of the relationship.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply