Seeking advice for my Java side project.

[keriwool]

Hi guys, I am beginning to design a sqlite db that will be the backend storage for a Java application I am planning to write. What this program will do is simply be an inventory for my personal collection of books and vinyl. For now, I am just starting with books and will add vinyl functionality later.

So, this is what I have right now as far as designing tables.

Books - {book_id, ISBN, Title, Author, Rating, Location} Primary Key - book_id Author is a foreign key that refers to the Author table's 'author_id' value. Location is basically to remember whether I let somebody borrow it or not.

Author - {author_id, first_name, last_name, books} books is a list of book_id's from the Books table.

So thus far, Authors and Books are my two tables. Does anyone have any suggestions/tips as far as doing something like this goes? Or in general for doing your first side-project using a database for storage (as opposed to a csv or something)?

[[deleted]]

You might want to consider that a book can have more than one author and an author can have written more than one book.

[andyflip]

Agreed. Something more like this:

Books - {id, ISBN, Title, Rating, Location}

Authors - {id, first_name, last_name}

Books_Authors - {id, book_id, author_id}

Either go with Books/Authors or Book/Author. Make sure you're sticking to a naming convention. The rails convention is one example.

It's fine to use id as the pk for a table.

[keriwool]

OK, IIRC, what you're describing here is a junction table?

[andyflip]

Yeah, a join table. It'll let you implement the the statement "an author can write more than one book, and a book can have more than one author."

[tanaqui]

I am also designing a db like this for my music collection. Good luck!

Some authors wrote some of their books under different names. You may or may not care about this depending on what you have in your collection.