0

We have an Order entity and a CreditCard entity.

The Order can have 0 or 1 CreditCards.

The CreditCard can have 1 or more Orders

I understand this to be a one-to-many relationship. BUT we anticipate for the majority of Orders to not be paid using a CreditCard, hence the Order table will be full of null references. My understanding being that this is a bad practice.

Is it possible to design the database/entities/mappings some other way to avoid this?

Alexandros B
  • 1,881
  • 1
  • 23
  • 36
  • I'm curious, why is it a bad practice to have a table with lots of nulls? – R. Martinho Fernandes Apr 26 '11 at 08:31
  • Isn't it? To be honest I think I am over thinking this problem and wasting my time... – Alexandros B Apr 26 '11 at 08:37
  • I'm not saying it isn't, but I fail to see why. I'm not confident enough in my DB knowledge to say that it's definitely ok, though. – R. Martinho Fernandes Apr 26 '11 at 08:43
  • 1
    If you really want to avoid that, you can treat it as many-to-many relationship. – driushkin Apr 26 '11 at 08:48
  • most my experience on the subject comes from university + internet, so I don't claim an expertise. I found [this](http://stackoverflow.com/questions/4336687/how-can-i-avoid-nulls-in-my-database-while-also-representing-missing-data) which is impossible with my nhibernate knowledge :( – Alexandros B Apr 26 '11 at 08:50
  • @driushkin i did this for a similar problem. But I don't like using a list to represent a single object. Plus it created a host of other problems with mvc that I want to avoid. – Alexandros B Apr 26 '11 at 08:54
  • @Circadian: Thanks a lot. So, the problem is 3-valued logic. Won't a boolean column `HasCreditCard` solve this? – R. Martinho Fernandes Apr 26 '11 at 09:06
  • @Martinho Fernandes If I remove the reference from `Order` and replace it with `HasCreditCard` how do I associate `Order` with the `CreditCard` object then? I want to have `Order.CreditCard` either a value or null, I just want to avoid that in the db. – Alexandros B Apr 26 '11 at 09:12
  • I think 'null' in the database comes from (and I'm not 100% sure on this) the fact that null actually used to take up data, but from SQL Server 2000 onward null doesn't actually use up any space so it's no long in-efficient to have nulls laying around. (I just remember reading something like that a long time ago, It's probably completely wrong.) – Phill Apr 26 '11 at 12:29
  • Also if you want to avoid a null reference in your code then it's probably a place for the Special Case pattern. http://martinfowler.com/eaaCatalog/specialCase.html – Phill Apr 26 '11 at 12:31

1 Answers1

1

I think that you are over thinking this. If it were me I would just have a Reference to a credit card in my order and in my credit card entity I would have a HasMany to Orders. If you want you can have a HasCreditCard boolean in your order that just checks if the credit card object in your order is null or not.

Cole W
  • 15,123
  • 6
  • 51
  • 85