Re: Tables and data question

In message <a87f231.0401211826.1909e02a_at_posting.google.com>, Dennis <eclecticmess_at_hotmail.com> writes
>If this is the wrong place to post this, I apologize.
>I am having a problem that I just can't seem to wrap my brain around
>regarding tables and their data. Part of the problem is that
>I am not sure I can even articulate the question, but here goes.
>*
>SQL Database: Book_Collection
>One table: tblBooks: Fields = Book_ID, Title, Author_ID,
>2nd table: tblAuthors: Fields = Author_ID, Author_Name
>*
>When I enter the Author_ID in a record in the Books table, how do I
>make sure it is the correct one that corresponds to the Author_ID in
>the Author's table? It seems to me that the only way I could ever
>accomplish this would be to make a print out of my Authors table and
>refer to it when I am entering records in my Books table. If I don't,
>I will end up with Author_Ids in the Book table that do not correspond
>to the Author-Ids in the Authors table. Does that make sense? Am I
>creating the tables incorrectly?
>*
>Any help you could give would be greatly appreciated.

This is one of the problems of using surrogate keys in a database, you need to make sure that you are using the right surrogate.

In my booklist database I first search the author table by name. When I am sure that I have the right name I take the Author_ID number and transfer that to my Title table. I have to do that because the real key in the Author table is the name, and Author_ID is only a surrogate.

You could print out the Author table and get the Author_ID from a printed list but it's more usual to use a program that divides the screen into an area for the master table (Author) and another area that shows all of the linked detail (Title) records. When you create a new detail record it automatically uses the Author_ID from the master record that is being displayed on-screen.

-- 
Bernard Peek
London, UK. DBA, Manager, Trainer & Author. Will work for money.