2

I've two tables which are connected to each other through a one to many relation. the problem is that the referenced table also has a foreign key of the second table!

I know it's confusing, So I'll show you a simple design of my database:

Authors

AuthorId      Name       DefaultBookId (FK)
--------      -------    -------------
1             John       1
2             Mike       3
3             Mig        5

Books

BookId        Title            AuthorId (FK)
--------      -------          -------------
1             TitleInfo1       1
2             TitleInfo2       3
3             TitleInfo3       2
4             TitleInfo4       1
5             TitleInfo5       3
6             TitleInfo6       3
7             TitleInfo7       1

Of course I have to make the AuthorId in the books table a foreign key and that's the normal case, but now I need to set a default book for every author so I'll define a foreign key on a new column (DefaultBookId) to specify the default book for each writer.

Now, both tables are dependent on each other so I can't delete any item unless I remove the relation between them and it doesn't feel the right thing to do!

Any ideas on whether this design flawed or not and what can I do about it ?

Mazen Elkashef
  • 3,430
  • 6
  • 44
  • 72

3 Answers3

3

I would implement this with a 3rd table that joins the other two. You'd end up with:

Authors

AuthorId      Name   
--------      -------
1             John   
2             Mike   
3             Mig

DefaultBook

AuthorId      DefaultBookId (FK)
--------      -------------
1             1
2             3
3             5

Books

BookId        Title            AuthorId (FK)
--------      -------          -------------
1             TitleInfo1       1
2             TitleInfo2       3
3             TitleInfo3       2
4             TitleInfo4       1
5             TitleInfo5       3

By putting a UNIQUE constraing on DefaultBook.AuthorId you can prevent each author from having more than one default. If you need to delete an author, simply delete his default and any books associated with him, then you can delete the author.

The one problem this has is that it's hard to enforce that each author has a default book, but that requirement is what led you to this problem in the first place.

Gabe
  • 84,912
  • 12
  • 139
  • 238
  • Can't believe no one mentioned such a clean solution .. THANK YOU =) (Y) .. but if you could point out what you meant by this point "The one problem this has is that it's hard to enforce that each author has a default book, but that requirement is what led you to this problem in the first place." because I don't see a problem with this solution! .. if it has cons that I don't see, could you please point it out – Mazen Elkashef Mar 09 '11 at 12:16
  • 1
    @IKashef - one thing it might be worth considering is that neither this model or your original model enforce that the default book was authored by the author who has it as his default (try saying that 5 times fast). Difficult to tell if this is a issue in your actual problem domain. – Damien_The_Unbeliever Mar 09 '11 at 13:02
  • @Damien +1 for the times I tried to read your comment seriously and couldn't stop laughing haha .. I'm really trying here! – Mazen Elkashef Mar 09 '11 at 13:28
  • It was hard to get it together :D .. about your comment! do you mean that the author's default book isn't authored by him ? .. if so how come, could you re-check the tables in my example again! every record matches of course – Mazen Elkashef Mar 09 '11 at 13:35
  • @IKashef - in your example, every record matches - but we've not done anything to *enforce* that, if its a genuine restriction (i.e. using your sample data, would it be okay for author 1's default book to be BookID 3?) – Damien_The_Unbeliever Mar 09 '11 at 13:57
  • of course not, and I enforce this rule in my code not on the database side .. when i add a book, the author is selected selected from a dropdownlist so I think it's safe! .. but if you have another point of view or a way to secure this point please share it with us =) – Mazen Elkashef Mar 09 '11 at 14:40
1

You could remove DefaultBookId from Authors, add an IsDefaultForAuthor column to Books, and then apply a constraint such that each author only has one book marked as IsDefaultForAuthor.

Of course, this doesn't address issues such as books which are authored by multiple Authors, but I'm assuming that this is a toy example you're presenting, rather than your actual system being modelled.

Damien_The_Unbeliever
  • 234,701
  • 27
  • 340
  • 448
  • Well I won't face the multiple authors because you're right I just simulated my problem on a simple and common design (the classic book &author example) .. I've already thought about it but how I'm going to do this part : "apply a constraint such that each author only has one book marked as IsDefaultForAuthor" ? – Mazen Elkashef Mar 09 '11 at 11:22
  • @IKashef - that becomes database (and version) dependent - in SQL Server, you'd use a filtered index (2008) or an indexed view (<2008). In Oracle, you'd use a function based index, I believe. – Damien_The_Unbeliever Mar 09 '11 at 11:30
1

In principle this design is ok, but of course you have already found a problem in praxis.

Here are some things you can try:

  • if your database supports it (oracle does) make your constraints deferred. This will only check the constraints on commit, so you can delete a book and its author in one transaction. (con: very database specific)

  • instead of the authors.defaultBookId use a books.isAuthorsDefaultBook. (con: hard to enforce exactly one default book per Author; should be possible with FunctionBased Indexes indexes and/or materialized views, which probably are db dependend)

  • drop the not null constraint on defaultBookId and trust your application to do it right (con: loosing some of the powers of a database)

Clarification:

Dropping the the not null constraint on defaultBookId allows to set it to null, remove the formerly referenced book, then remove the author. For creation the same works, but in opposite order.

Jens Schauder
  • 77,657
  • 34
  • 181
  • 348
  • About your first option, does SqlServer supports it ? .. I'm sorry i didn't get the third option, what does the not null constraint has to do with the referential integrity of the database, did you mean to drop the FK constraint ? – Mazen Elkashef Mar 09 '11 at 11:25
  • And the second option, seems the best and easiest solution that adds no complication to my design (I, Daimen and you already thought about it which proves it's simplicity) but It's hard to change the default book's settings, I mean each time I have to change the default book I'll have to connect n-times to set IsDefault to the rest of the books under the same author, and also when I fetch the author's info instead of just using the DefaultBookId to display the default's information, I'll have to loop through the books and check for IsDefault == true! .. what do you think =) ? – Mazen Elkashef Mar 09 '11 at 11:28
  • you can set and read the isDefault value easily with a single update/select statement. So I don't see to much of a problem here. – Jens Schauder Mar 09 '11 at 12:40
  • Looks like sql server does NOT support deferrable constraints: http://stackoverflow.com/questions/998095/deferrable-constraints-in-sql-server – Jens Schauder Mar 09 '11 at 12:41