6

I have a small question concerning with how I should design my database. I have a table dogs for an animal shelter and I have a table owners. In the table dogs all dogs that are and once were in the shelter are being put. Now I want to make a relation between the table dogs and the table owners.

The problem is, in this example not all dogs have an owner, and since an owner can have more than one dog, a possible foreign key should be put in the table dogs (a dog can't have more than one owner, at least not in the administration of the shelter). But if I do that, some dogs (the ones in the shelter) will have null as a foreign key. Reading some other topics taught me that that is allowed. (Or I might have read some wrong topics)

However, another possibility is putting a table in between the two tables - 'dogswithowners' for example - and put the primary key of both tables in there if a dog has an owner.

Now my question is (as you might have guessed) what the best method is of these two and why?

Bigalow
  • 325
  • 2
  • 14

6 Answers6

4

The only solution that is in keeping with the principles of the Relational Model is the extra table.

Moreover, it's hard to imagine how you are going to find any hardware that is so slow that the difference in performance when you start querying, is going to be noticeable. After all, it's not a mission-critical tens-of-thousands-of-transactions-per-second appliation, is it ?

Erwin Smout
  • 18,113
  • 4
  • 33
  • 52
  • I disagree. A nullable column as the foreign key is a valid and common solution. adding a relation table is necessary only for many to many relationship, clearly that is not the case here. – Zohar Peled Feb 11 '16 at 07:56
  • 2
    I couldn't care less about you agreeing or not. You clearly have no clue. That SQL NULLs are a "common" solution, I ***know*** that. That they are a "valid" solution depends on your notion of "valid". SQL NULLs are not relational, therefore, if what you want to do is design a ***relational*** database, then using SQL NULLs is ***not*** "valid", period. – Erwin Smout Feb 11 '16 at 08:05
  • No it isn't, in this case I am more worried about making it clear how the database is put together if I have to touch it again after a long pause or am putting the project in someone else's hand. However let's say it was a 'mission-critical tens-of-thousands-of-transactions-per-second appliation' (just out of curiousity) would you than go for a nullable foreign key? – Bigalow Feb 11 '16 at 08:11
  • @ErwinSmout: Well, can you explain why every enterprise database allows for nullable columns if they are **not valid**? Is it me that have no clue or Oracle, Sql Server, MySql, MSAccess, postgreSql and every other I forgot to mention by name have no clue? Did you even consider the possibility that you might be wrong? The way I see it, when major products such as Oracle and Sql server allow nullable columns to be used as foreign keys, I'm happy to suggest it when it's the solution I consider to be the best regardless of what **you** care about. – Zohar Peled Feb 11 '16 at 08:17
  • 3
    @Bigalow It is almost always simpler to understand and use a database without NULLs. The things that rows in tables state about the situation are much simplier. [(Just try to say what a row says when in a table, for the design with NULL vs another table.](http://stackoverflow.com/a/27682724/3404097) Because SQL DBMSs have poor constraint/integrity facilities one of the main reason NULLs get used is when they allow a design with declaratively expressed constraints vs complexly procedurally expressed constraints. (No such NULL advantage here though.) – philipxy Feb 11 '16 at 08:32
  • 1
    @bigalow your curiosity question : If I am forced to use this or that particular SQL engine, and I know that its JOIN performance is so poor (even with the best of all possible physical designs) that it risks making the performance requirements inachievable, then of course I would. – Erwin Smout Feb 11 '16 at 08:50
  • 3
    @zohar : yes I can explain that but not within the space granted to me in the comments here. The gist is NULLs were invented 40 yrs ago as a hack to overcome the performance limitations of the hardware of those days. And believe it or not, the vast majority of the engineers working on Oracle, SQL Server and what have you, know ***full well*** that these NULLs they are forced to keep supporting for historical reasons, are in fact a major violation of the RM. – Erwin Smout Feb 11 '16 at 08:57
  • 1
    @ErwinSmout you seem to be confused between Bigalow and me. What if you need to store data where some values are optional? would you prefer to add a related table for each optional value instead of allowing null values? btw, I didn't say I'm right because Oracle and Microsoft say so, I think I'm right because **every** enterprise database say so. I've been working with databases for a long time, never heard of any database that doesn't allow null values. – Zohar Peled Feb 11 '16 at 08:58
4

I agree with Philip and Erwin that the soundest and most flexible design is to create a new table.

One further issue with the null-based approach is that different software products disagree over how SQL's nullable foreign keys work. Even many IT professionals don't understand them properly so the general user is even less likely to understand it.

nvogel
  • 24,981
  • 1
  • 44
  • 82
2

The nullable foreign key is a typical solution.

The most straightforward one is just to have another table of owners and dogs, with foreign keys to the owner and dog tables with the dog column UNIQUE NOT NULL. Then if you only want owners or owned dogs you do not have to involve IS NOT NULL in your queries and the DBMS does not need to access them among all owners and dogs. NULLs can simplify certain situations like this one but they also complicate compared to having a separate table and just joining when you want that data.

However, if it could become possible for a dog to have multiple owners then you might need the extra table anyway as many:many relationship without the UNIQUE NOT NULL column and the column pair owner-dog UNIQUE NOT NULL instead. You can always start with the one UNIQUE NOT NULL and move to the other if things change.

philipxy
  • 14,867
  • 6
  • 39
  • 83
  • So if I get you right, making an extra table has the advantage that I would catch a possible many to many relationship in the future? – Bigalow Feb 11 '16 at 07:53
  • 1
    It does, but it also lets you access owners-dog pairs without running through all owners or dogs, at the cost of a join. The idea is to use the most straghtforward design and optimize when & if needed. See my edited answer. – philipxy Feb 11 '16 at 08:02
2

In the olden days of newsgroups, we had this guy called -CELKO- who would pop up and say, "There is a design rule of thumb that says a relational table should model either an entity or a relationship between entities but never both." Not terribly formal but it is a good rule of thumb in my opinion.

Is 'owner' (person) really an attribute of a dog? It seems to me more like you want to model the relationship 'ownership' between a person and a dog.

Another useful rule of thumb is to avoid SQL nulls! Three-valued logic is confusing to most users and programmers, null behavior is inconsistent throughout the SQL Standard and (as sqlvogel points out) SQL DBMS vendors implementation things in different ways. The best way of modelling missing data is by the omission of tuple in a relvar (a.k.a. don't insert anything into your table!). For example, Fido is included in Dog but omitted from DogOwnership then according to the Closed World Assumption Fido sadly has no owner.

All this points to having two tables and no nullable columns.

onedaywhen
  • 55,269
  • 12
  • 100
  • 138
0

A nullable column used for foreign key relationship is perfectly valid and used for scenarios exactly like yours.

Adding another table to connect the owners table with the dogs table will create a many to many relationship, unless a unique constraint is created on one of it's columns (dogs in your case).

Since you describe a one to many relationship, I would go with the first option, meaning having a nullable foreign key, since I find it more readable.

Zohar Peled
  • 79,642
  • 10
  • 69
  • 121
  • A dog can't have more than one owner (on administration level at least). I edited the question to clarify that. – Bigalow Feb 11 '16 at 07:51
  • Well, if a god can't have more then one owner then go with a nullable foreign key, as almost all the answers suggests. – Zohar Peled Feb 11 '16 at 07:54
0

I wouldn't do any extra table. If for some reason no nulls allowed (it's a good question why) - I would, and I know some solutions do the same, put instead of null some value, that can't be a real key. e.g NOT_SET or so.

hope it helps

Daniel
  • 1,364
  • 1
  • 11
  • 18