Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a coworker who's insisting on using associative tables to create nullable associations. For example, we have this table :

cm.accounts
----------
id:int (PK, identity)
...

sm.services
-------------------
id:int (PK, identity)
...

cm.account_services
-------------------
fk_account_id:int (FK, cm.accounts.id)
fk_service_id:int (FK, nullable, sm.services.id)

and he's arguing that creating rows like

INSERT INTO cm.account_services 
            (fk_account_id, fk_service_id) VALUES 
            (123, NULL);

is perfectly fine. I disagree. The purpose of this "design" is to allow a temporary (or future) "relationship" to be created, and be updated later on. It doesn't have to be this way and this is my coworker's decision and not a requirement.

I believe that this is not good practice at all, and simply adds more complexity and undefined behaviours than necessary. I know this question may raise an animated discussion and I'm not looking for an opinion, but facts.

By using references, can an expert help break this tie?

Thanks.

** NOTE **

Just found this paragraph here :

Nullable foreign keys are not considered to be good practice in traditional data modelling [...] This is not a requirement of Hibernate, and the mappings will work if you drop the nullability constraints.

** EDIT **

What I would suggest is something like this :

cm.accounts
----------
id:int (PK, identity)
...

sm.services
-------------------
id:int (PK, identity)
...

cm.account_services
-------------------
fk_account_id:int (FK, cm.accounts.id)
fk_service_id:int (FK, sm.services.id)
link_type:int    // or an enum. i.e. "validated", "deleted", "invalid", etc.

So we could identify (instead of null values) what kind of relationship the two table rows have, thus giving more verbosity in the business logic, etc.

share|improve this question
    
I think this is entirely subjective to the business process being modeled. I don't see any point in adding a record to a relationship table to represent the absence of a relationship. –  SQLFox Jun 12 '13 at 14:20
    
Exactly. The problem we're having is that, once a relationship is made, it cannot (should not) be deleted, unless one or the other is deleted. Thus why the link_type suggestion. As opposition to when a relationship does not yet exist, there should not be a row in cm.account_services at all. Because there is no association to begin with. –  Yanick Rochon Jun 12 '13 at 14:34
    
account_services conveys information by linking accounts to services. Can't find any information increment by linking an account to a NULL service. –  user16484 Jun 12 '13 at 14:46
    
@user16484, I agree. But I'd need evidence to support this, otherwise this is merely a religious war. –  Yanick Rochon Jun 12 '13 at 14:52
1  
I can only remember of the Occam's razor principle. "The razor states that one should proceed to simpler theories until simplicity can be traded for greater explanatory power". Those extra rows with the nulls will mean more disk space used, larger table and indexes, leading to less performance, yet is there one advantage that can be stated on having them compared to simply not having them? –  user16484 Jun 12 '13 at 15:12
show 1 more comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.