Nullable FK with one to many relationship

[jaryszek]

Hi Guys,



i noticed that my problem results from one to many relationship with nullableFK.

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.

To avoid them i can create a lot of one to many relationships from Topologies :
Topology-->Intersection Table --> Enitity specific for topology,
example:




but i am not sure it is a good approach.

What do you think about nullabe fields? This is a good approach or not?
Better to use intersection tables to avoid nulls?

Your opinion is very important to me.

Best Wishes,


Jacek

[jaryszek]

Anyone ?

Jacek

[isladogs]

You haven't said what your problem is so this is no more than a guess.
Using inner joins, it sounds like a query for Topology6 containing all those fields won't have a record.
If that's the issue, use outer joins in your queries or relationships

[jaryszek]

Hi ridders52,

My problem is if nullabe FK should be use in data model design.

Using inner joins, it sounds like a query for Topology6 containing all those fields won't have a record.

exactly.

If that's the issue, use outer joins in your queries or relationships

What do you mean?

Jacek

[isladogs]

When you create a join between two tables A and B you have three choices
1. Where join fields in both tables are equal. This is an INNER join and it's what you have for all tables shown
2. Include all records from table A and only those from table B where joined fields are equal
3. Include all record from table B and only those from table A where joined fields are equal
Options 2 & 3 are OUTER joins and will be shown with an arrow pointing left or right.
As a result, they are also known as LEFT and RIGHT joins or LEFT OUTER join etc

Modify so your joins have arrows flowing away from t_topologies if that is your main table.
Be consistent or you will get an error about inconsistent joins

[jaryszek]

ridders52, thank you!

I know what you mean here.
Hmm maybe this is good idea.
But this is only how to see the data.

And i am asking about structure of data. With nullable FK.
Because for Topologie 5 for example there will not be supported systems so in Clients_ID i will have nulls!

Problem is like here:

https://stackoverflow.com/questions/...d-or-duplicate

What do you think about that?
Best,
Jacek

[CJ_London]

if you have defined your relationships to maintain referential integrity, then you cannot have a null FK. So consider whether you have applied this requirement correctly given the rules of the business. i.e. if the rule is that a table must have EITHER field1 OR field2 populated then you cannot maintain referential integrity.

[jaryszek]

Exactly, Table must have either Field1 or Field2.

It is not possible to build it in other way.

So if i can not maitain RI my database sucks?

Best,
Jacek

[CJ_London]

So if i can not maitain RI my database sucks?

No, it only sucks if it doesn't follow the real life business rules of the organisation. I wouldn't get hung up on it.

Sometimes (like this) maintaining RI within the database functionality is not possible or practical - but you can still do it with code to ensure a) that your table is saved with either Field1 or Field2 is completed. and b) code in the forms which manage your lookup tables to prevent a record being deleted when that record has children. Many enterprise scale systems simply don't allow the deletion of a parent record (remember they all work through forms of one sort or another) - but they might have a 'do not use' flag so once set it no longer appears as a choice in combos, listboxes etc

Only other way, and don't know if this is practical for you, is to merge the two parent tables into one, then you only need the one field in the child table - but you've only passed the problem on if one of the parent tables has a required field, and the other doesn't - although you can get round it by having a 'not required' record in that parent table which the non requiring table can use as a default.

It's a bit like normalisation. It's necessary but can be taken too far. For example names, say John Smith. Makes sense to split it into two fields, but there are plenty of Johns and plenty of Smiths - would it be sensible to have a list of first names and a list of surnames so your 'name' table just stores the FK for each?

[jaryszek]

thank you Ajax,

i learned a lot form your post - nice experience!

I was thought about creating this parent table (i do not feel how it should looks, do you have any example?)
but my general approach will be like with Topologies table.

So i will create assiocations tables and thanks to it avoid nulls.

Thank you,
Jacek