0

I hope you are all fine,
I have read before that if e.g. I have two tables (cars, colors), each car has one color, I can make colors table with one field "color" (char primary key) and its values should be ("green", "blue", ...) and when I query cars table I will retrieve the color (the color name not surrogate id) without need to join colors table! my question is can I use the same idea with database has two tables (books, authors) using author name from authors table as a primary key so when I query books table I retrieve the author name without joining the two tables... given I have very limited number of books and authors (less than 1000 book)
Thank you.

ahmed
  • 13
  • 5
  • 2
    As a primary key it would be unique so if you have two books by the same author, if the author value is the PK then that repetition would not be allowed. Why not simply use an Auto Increment integer value as the key? – Martin Jul 10 '17 at 11:57
  • What happens when two different authors have the same name? – reaanb Jul 10 '17 at 11:58
  • Car-color analogy is totally wrong. I can have 2 cars of same color. This satisfies 'each car has one color' but your primary key(color) will be messed up. – Prabhat G Jul 10 '17 at 12:02
  • @Martin there would be two tables books( ID,book_name,author) author is foreign key .... and authors(author) author is primary key char ... – ahmed Jul 10 '17 at 12:02
  • @reaanb there shouldn't be since these names would be shown, and if there are two authors with the same name I have to add family name e.g. to differ them even if i use surrogate key! – ahmed Jul 10 '17 at 12:05
  • @ahmed My point is that a varchar foreign key can't distinguish between two identically-named authors, e.g. Russ King, Jim Lacey (just google for more examples). This is one of the reasons that surrogate identifiers are useful. – reaanb Jul 10 '17 at 12:08
  • 1
    To be precise, it's not the varchar datatype that's the problem but the use of a non-identifying attribute. Identifying varchar keys are valid, though many people advise against them for size/performance reasons. – reaanb Jul 10 '17 at 12:12
  • I always think that using a varchar as primary key is a terrible idea let along a non-identifying one. You have to think that it is a system and you are defining a data-type just out of laziness. Read this thread: https://dba.stackexchange.com/questions/80806/varchar-primary-key-mysql – Jorge Campos Jul 10 '17 at 12:16
  • @reaanb even if I used surrogate key I wouldn't distinguish as a user between them!, it would be a problem until I change one author name to differ them, I will consider it wrong to put two similar author names that can not be distinguished by the end user... – ahmed Jul 10 '17 at 12:19
  • @reaanb as for performance ... I don't need to join the other table of authors every time I am just querying the books table to just get the author name, I can also make author field in books table (index) which can increase the performance as i search by '=' ... not 'like' – ahmed Jul 10 '17 at 12:25
  • @ahmed The UI can show additional content, such as a photo or a list of books for each author by which they could be distinguished. Duplicates happen and deciding author names shouldn't be up to the programmer or user. See also [What are the design criteria for primary keys?](https://stackoverflow.com/questions/3632726/what-are-the-design-criteria-for-primary-keys) – reaanb Jul 10 '17 at 12:38

1 Answers1

1

Firstly, (cars, colors) scenario you've mentioned is not a well designed database. Consider this scenario:

Id  Car    Color(PK)
--  ---    -----
1  Suzuki  Black 
2  Mazda   Green
3  Audi    Black

Primary key says : it should be a unique not null identifier.

Now, is Color your primary key ?

Similarly consider your Book, Author

Id  Author(PK)      Book
--  ------          -----
1  J.K.Rowling      Harry potter
2  Dan Brown        Inferno
3  Rudyard Kipling  Jungle book 
4  Dan Brown        The da vinci code

Author is NOT unique.

Your design should be :

Author
Id   Name
1    J.K.Rowling
2    Dan Brown
3    Rudyard Kipling

Books
Id    Name
1     Harry potter
2     Inferno
3     Jungle book 
4     The da vinci code

Mapped_table
Id  Author_Id  Book_Id
1   1          1
2   2          2
3   2          4
4   3          3
Prabhat G
  • 2,974
  • 1
  • 22
  • 31
  • something misunderstood here, what I meant is there are two tables first table is: books( ID,book_name,author) author is foreign key and the second table is: authors(author) author is primary key and contains the author name – ahmed Jul 10 '17 at 12:29
  • 1
    @ahmed please reread what Prabhat says, your current database structure is entirely unsuitable for having primary keys. Stop searching for a solution to your current structure and fundamentally fix the issues with your database structure. – Martin Jul 10 '17 at 15:12