I am working on a library system. I have a book table in which there are 12 columns including author, title, publication etc. Among those 12 there are 2 columns book_id and isbn that are unique. When a library get n copies of a same book the only columns that are going to be unique are book_id and isbn. All other columns are going to repeat n times. Is this a bad design. Is there a way to remove this redundency? I thought of creating another table where I could store uniquely all the redudent columns and seperate book_id and isbn form it. Should I do that or is the table just fine as it is?
Advertisement
Answer
I think you should use two different tables.
In one table you can store unique keys like book_id, isbn_id. And in another table you can use one of these book_id or isbn_id for a reference foreign key. Or other way is you can create an auto increment id also in first table for foreign key reference to second table.
In second table you can keep all other 10 columns + 1 column for reference from 1st table.
This will help you to maintain redundancy and this first table can be also used for mapping as well if in future you want to extend database tables.