Fundamental design question

[jgnasser]

Here is a fundamental database design question. I have learnt that one of the elements of good database design is to avoid redundancy by creating more tables and referencing them using foreign keys. The query I have then is what is the procedure of data entry? Take a look at this example:

Instead of having this:

tblBooks
BookID, Title, Author
1, Mastering Access, Antony
2, SQL Basics, John
3, Intro to Computers, Anthony

I have

tblBooks
BookID, Title, AuthorID
1, Mastering Access, 1
2, SQL Basics, 2
3, Intro to Computers, 1

and

tblAuthors
AuthorID, Name
1, Antony
2, John

This looks good and is better design because only authorID (not author name) is repeated in tblBooks. However, how do I now enter the data for a particular book? If referential intrigty is enforced, tblBooks does not accept AuthorID unless the record already exists in tblAuthors. If not then entering the authorID makes no sense and you don�t have it since it�s a foreign key. Someone please clarify.

 

[The_Doc_Man]

Once you have normalized your database, you start running into cases where you need to fill in both tables at the same time. You ALSO run into wrinkles related to 1-to-1 and many-to-1 relationships. I think your design lacks one more element.

tblBook
fldBookID, long, could be autonumber, PK
fldBookTitle
fldBook... etc

tblAuthor
fldAuthorID, long, could be autonumber, PK
fldAuthorName

tblWhoWrote
fldBookID, long, FK
fldAuthorID, long, FK

This allows you to define a book with multiple authors.

Now, as to how you do this...

You need a parent form for the books. You (probably) need a child form for the authors. The child form will be a query based on joining tblAuthor to tblWhoWrote through the fldAuthorID. Then you allow the child form to become a sub-form of the parent. You identify the book in the parent form. Then you identify the author in the sub-form. You would link the two on the fldBookID, which is a sub-form property. (Link Parent, Link Child - look it up in the Help Files)

A second wrinkle is that if you are entering a book for a new author, you might want to take the approach of defining authors first through a separate and independent Authors form. If you always entered authors first, you could even make author entry in the book form become a drop-down selection if you wanted.

The key to understanding why this works is to remember that you can build a form from a table OR a query. So if you build a join query of the tblWhoWrote and tblAuthor tables to get the necessary fields, your sub-form would have all it needs. You would get the fldBookID filled in because that would be your parent/child link field. You would get the fldAuthorID filled in because that would be part of the Join Query result.

 

[jgnasser]

Thanks The_Doc_Man for this contribution. The third table you added was really crucial. I have taken a shot at it and it works but with 2 wrinkles. First, when I query for a particluar book, in order to get both (or more) authors, the book has to be listed twice. Any way round this? Secondly, the data entry still doesnt work. I've tried using a subform. I hereby attach my sample.

 

[jgnasser]

Look in the samples section for a sample many-to-many database.

Pat, I have searched and found the thread om Many-tomany relatioships and downloaded the zip file but just like all the other zipped files I have downloaded from this forum, I cant open it, I get an error that its an invalid archive. I hope the fact that I am downloading into Linux and opening it in Windows has nothing to do with that.

 

[The_Doc_Man]

I hereby attach my sample.

But I cannot read your sample. My military site screens my downloads.

The issue is probably addressed in whatever Pat has sent to you. Trust her to give you good references. She's quite trustworthy in that regard. She won't mislead you unless you mislead her first.

As to data entry, the only wrinkle that should vex you is that in my suggested scheme, authors should be defined first from a separate form IF you wanted to do a drop-down type selection.

You also have to define the sub-form based on a query that includes the linking table entry. Then use the book ID as the link between the parent (book table) form and the child (author join-query) sub-form. I understand if you have trouble. Perhaps you should let the sub-form control be created by the embedded-form wizard. It will ask the right questions about linking the parent and child forms.

By the way, that's good advice all over. Use the wizards to build things, then go back and fix what was built wrong. But thank the wizard mentally for what it did right, which was to save you a lot of time and energy by building most of what you wanted OK. I've always found it easier to start from the wizard and customize from there, rather than starting from scratch.