setting up tables and relationships for a personal book inventory

Question

setting up tables and relationships for a personal book inventory

Lisa Reid 0

Is there someone that I could contact (preferably in person but could try to do online) to assist me in setting up a proposed table layouts and relationships for a massive book inventory that I have acquired and need to better organize. I have used Access but have never before set relationships up and am having some trouble understanding the process. I have created proposed layouts and possible relationship needs for each table in an Excel spreadsheet to better see what I am trying to get my end results to be and make any suggested changes. Any suggestions?

0 comments

3 answers

Your answer

Answer 1

I have used Access but have never before set relationships up and am having some trouble understanding the process.

For a simple introduction to modelling data in a relational database you might like to take a look at Relationships.zip in my Dropbox public databases folder at:

https://www.dropbox.com/scl/fo/0scigd3r48hx5xrev2jrf/AB0-GMdTgMAO5O1cGdr3QW0?rlkey=ib6bs6g9jqcrywwzivur3265t&dl=0

This little demo file uses a simple hypothetical medical prescriptions database, and illustrates how relationships are built up across a database to model the underlying reality with related tables, each of which models a real-world entity type. You'll see that some tables model relationships rather than physical entity types. In the database relational model a relationship type is really just special kind of entity type.

Where a table models a relationship type the relationship is usually many-to-many, between two or more tables. For example the DrugForms table models the many-to-many relationship between Drugs and FormTypes, as a drug might be available in one or more forms, tablet, capsule etc., and each form will apply to many drugs,

Many relationships will be one-to-many, however, in which case the relationship type is not modelled by a separate table, but simply by means of a foreign key in the referencing table referencing the primary key of the referenced table. An example of this is the relationship between Patients and PatientPrescriptions. Each patient can have many presriptions, but each prescription can apply to only one patient, so the PatientID foreign key in PatientPrescriptions references the PatientID primary key of Patients. Note that a numeric 'surrogate' primary key must be used her rather than the patient's name as personal names can be duplicated, even if combined with other columns. I was once present at a clinic when two patients turned up within minutes of each other, both female, both having exactly the same names, and both having the same date of birth.

Answer 2

George Hepworth 23,120 Volunteer Moderator

While I agree with Duane's comments, I do just happen to have two such databases actively in development. Both are online as well as being in Access.

I can share some ideas, but it's a rather large project. You can explore one of them here: https://gpcdata.com/LTFCatalog

Lisa Reid 0 Reputation points

2026-07-15T22:07:02.76+00:00

I reviewed your attachment and that looks like a useful tool, but I am trying to record information for the books I have in Kindle, Apple and the many others I have (I'm a Book Nerd) such as: Series Order by Author's suggested reading order; whether or not the rest of a series of whatever book I choose to read is free on Kindle or the Library or if I have to purchase other books in the series; how many pages so I know about how long I need; whether there is a Prequel or Novella's not listed in series that are related to the book, so I get all items related to a book; and many other things. I was able to create relationships to each of my associated tables to the books table (Primary) using the wizard but can't get the others. I just don't understand how to create the ID for other items (besides the whole table) everything I have tried tells me there is an error that the fields aren't the same. Would I go into the table and set up an entry that says what I'm trying to accomplish: Example 2 Tables: Author to Series (so that I can see list of all series by that Author). In the create relationship box side one I would put Author and Author ID; on side two I would put Series and what? ID doesn't work, only gives me a one to one, I want a one to many I thought and I don't know what to use?? Or would that be a one to one? Maybe that's my problem. Any further help would be appreciated.
George Hepworth 23,120 Reputation points Volunteer Moderator

2026-07-16T13:41:16.5433333+00:00

Okay, thanks. That's primarily a matter of a different media: digital. In fact, the library to which I posted the link already has

Expanding the list is a simple matter of adding one record to one table.

Most of the rest of the table structure is common to what you describe. For example, PageCount and IsMultiVolumeSet (or your term "Series") are common attributes, as is, of course, Author and co-Author. Also, you don't specify, but Genre, or Category, is also a common attribute. In other words, the bulk of the design is similar or the same. You only need to add attributes that are specific to your requirements. That is the reason "one size does NOT fit all", in database design.

One thing you mention that is quite unique to your library is this: "; whether there is a Prequel or Novella's not listed in series that are related to the book, so I get all items related to a book" That's definitely an additional requirement calling for a separate table. The good news is that including it in a properly Normalized design would be very straightforward.

Feel free to contact me via email and we can continue a discussion. I can share the basic Entity Relationship Diagram (ERD) for a library, so that you can expand it to fit your requirements.

ghepworth "at" gpcdata "dot" com

Answer 3

Duane Hookom 26,935 Volunteer Moderator

I applaud you for asking and using Excel to gather specifications. Have you done a search for “book collection ERD database”? I hate sending people to search but it makes more sense than to try provide a concise answer to a somewhat vague question. I suggest searching and then coming back here with specific questions. I would recommend settling on a good naming convention during your planning.

Lisa Reid 0 Reputation points

2026-07-15T21:53:15.4533333+00:00

Thanks, I will check out "Book collection ERD"
Deleted

This comment has been deleted due to a violation of our Code of Conduct. The comment was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.