CODEWITHSUNDEEP
databasedatabase-design
Mazen Elkashef
Mazen Elkashef

Reputation: 3499

A 2-way relationship (a database design flaw)

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 ?

Upvotes: 3

Views: 4134

Answers (3)

Jens Schauder
Jens Schauder

Reputation: 81988

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.

Upvotes: 1

Gabe
Gabe

Reputation: 86808

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.

Upvotes: 3

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239804

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.

Upvotes: 1

Related Questions